Beastmode Including exceptions to % calculation
Hi,
I'm trying to calculate % right first time to remove exceptions ie where some "discrepancies" are right first time (RFT) and other classed as not RFT. However, I wish to include the data points from those which are deemed RFT in the overall calculation.
This calculation works but doesn't allow me to exclude certain datapoints:
1-
COUNT (DISTINCT CASE
WHEN `HOLD_NAME` IN ('Incomplete Info' ,'PO – INVOICE HOLD', 'PENDING PO OVERRIDE' , 'CREDIT HOLD- INVOICE HOLD','CCCL Info Incomplete')
OR HOLD_NAME
like '%Discrepancy%'
OR `RETURNCODE` is not null
THEN `Study Number` END) / COUNT(DISTINCT `Study Number`)
Examples being Discrepancy 121901 / 121325 and 121275 are all RFT but all other discrepancies are wrong.
Is it possible to do this in the same beastmode?
If I filter them out it reduces the overall number and skews the final %.
I tried the below but it returned a 0% RFT which isn''t right.
1-
COUNT (DISTINCT CASE
WHEN `HOLD_NAME`<>'Discrepancy 121901'
OR `HOLD_NAME`<> 'Discrepancy 121275'
OR `HOLD_NAME`<> 'Discrepancy 121325'
OR `HOLD_NAME`<> 'Discrepancy 121566'
OR `HOLD_NAME` IN ('Incomplete Info' ,'PO – INVOICE HOLD', 'PENDING PO OVERRIDE' , 'CREDIT HOLD- INVOICE HOLD','CCCL Info Incomplete')
OR `HOLD_NAME`like '%Discrepancy%'
OR `RETURNCODE` is not null
THEN `Study Number` END) / COUNT(DISTINCT `Study Number`)
Best Answer
-
@Katie_Forrest_2022 Your second beast mode is returning 0% because you are using a combination of OR and <> in you case statement, which is logically true for every row in your dataset. So for example, Discrepancy 121901 is not Discrepancy 121275, so it meets the criteria of your second exception.
I generally prefer to use additional WHEN... THEN... clauses in case statements over OR operators because it allows you to be much more surgical about classifying rows. It's also helpful that WHEN... THEN... clauses are evaluated top to bottom, whereas the order does not matter with criteria separated by OR operators.
I'd suggest structuring your beast mode like this:
count(distinct case when `HOLD_NAME` in ('Discrepancy 121901','Discrepancy 121275', 'Discrepancy 121325','Discrepancy 121566') then `Study Number` when `HOLD_NAME` not in ('Incomplete Info' ,'PO – INVOICE HOLD', 'PENDING PO OVERRIDE' , 'CREDIT HOLD- INVOICE HOLD','CCCL Info Incomplete') and `HOLD_NAME` not like '%Discrepancy%' and `RETURNCODE` is not null then `Study Number` end) / count(distinct `Study Number`)
2
Answers
-
@Katie_Forrest_2022 Your second beast mode is returning 0% because you are using a combination of OR and <> in you case statement, which is logically true for every row in your dataset. So for example, Discrepancy 121901 is not Discrepancy 121275, so it meets the criteria of your second exception.
I generally prefer to use additional WHEN... THEN... clauses in case statements over OR operators because it allows you to be much more surgical about classifying rows. It's also helpful that WHEN... THEN... clauses are evaluated top to bottom, whereas the order does not matter with criteria separated by OR operators.
I'd suggest structuring your beast mode like this:
count(distinct case when `HOLD_NAME` in ('Discrepancy 121901','Discrepancy 121275', 'Discrepancy 121325','Discrepancy 121566') then `Study Number` when `HOLD_NAME` not in ('Incomplete Info' ,'PO – INVOICE HOLD', 'PENDING PO OVERRIDE' , 'CREDIT HOLD- INVOICE HOLD','CCCL Info Incomplete') and `HOLD_NAME` not like '%Discrepancy%' and `RETURNCODE` is not null then `Study Number` end) / count(distinct `Study Number`)
2 -
Thanks for a super speedy and workable solution!!!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 102 SQL DataFlows
- 626 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 753 Beast Mode
- 61 App Studio
- 41 Variables
- 692 Automate
- 177 Apps
- 456 APIs & Domo Developer
- 49 Workflows
- 10 DomoAI
- 38 Predict
- 16 Jupyter Workspaces
- 22 R & Python Tiles
- 398 Distribute
- 115 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 130 Manage
- 127 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 11 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 110 Community Announcements
- 4.8K Archive