Beast Mode not calculating for Current Year to Date
I am stumped on this one...
I created a beast mode to calculate the number of rejections as a percentage of sales. Specific lines of data have reason codes to flag what type of transaction it is. So F, H, etc are specific type of rejection lines. The "0" is a normal sales transaction. The beast mode below calculates properly when the date range is set to "All Time" or "Previous Year". For some reason it does not calculate when it is set to "Current Year". I have checked my source data and the date is in the correct format. I am not seeing a reason it should not calculate for "Current Year" (2019).
(ABS(SUM(case when `Code` = 'F' then `quantity` end))
+
ABS(SUM(case when `Code` = 'H' then `quantity` end))
+
ABS(SUM(case when `Code` = 'I' then `quantity` end))
+
ABS(SUM(case when `Code` = 'J' then `quantity` end))
+
ABS(SUM(case when `Code` = 'T' then `quantity` end)))
/
(SUM(case when `Code` = 0 then `quantity` end))
Best Answer

Try adding some else 0 clauses to your case statments:
(ABS(SUM(case when `Code` = 'F' then `quantity` ELSE 0 end))
+
ABS(SUM(case when `Code` = 'H' then `quantity` ELSE 0 end))
+
ABS(SUM(case when `Code` = 'I' then `quantity` ELSE 0 end))
+
ABS(SUM(case when `Code` = 'J' then `quantity` ELSE 0 end))
+
ABS(SUM(case when `Code` = 'T' then `quantity` ELSE 0 end)))
/
(SUM(case when `Code` = 0 then `quantity` ELSE 0 end))Essentially, if you are missing any of these Codes for current year data, then this beastmode will stop calculating. By adding the "ELSE 0" you are giving the beastmode a way forward when the particular code does not exist in the dataset
“There is a superhero in all of us, we just need the courage to put on the cape.” Superman2
Answers

Try adding some else 0 clauses to your case statments:
(ABS(SUM(case when `Code` = 'F' then `quantity` ELSE 0 end))
+
ABS(SUM(case when `Code` = 'H' then `quantity` ELSE 0 end))
+
ABS(SUM(case when `Code` = 'I' then `quantity` ELSE 0 end))
+
ABS(SUM(case when `Code` = 'J' then `quantity` ELSE 0 end))
+
ABS(SUM(case when `Code` = 'T' then `quantity` ELSE 0 end)))
/
(SUM(case when `Code` = 0 then `quantity` ELSE 0 end))Essentially, if you are missing any of these Codes for current year data, then this beastmode will stop calculating. By adding the "ELSE 0" you are giving the beastmode a way forward when the particular code does not exist in the dataset
“There is a superhero in all of us, we just need the courage to put on the cape.” Superman2 
This worked. Thank you for the explanation. Good to know that the beast mode will stop calculating at any part of the formula once it does not find that data in the time frame of the data set. I assumed that the formula in total would never equal zero so I dropped the "else 0" off. I now know that is not the case. Thanks again.
0
Categories
 All Categories
 1.5K Product Ideas
 1.5K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 283 Workbench
 4 Cloud Amplifier
 4 Federated
 2.7K Transform
 90 SQL DataFlows
 565 Datasets
 2K Magic ETL
 3.4K Visualize
 2.3K Charting
 593 Beast Mode
 13 App Studio
 28 Variables
 588 Automate
 143 Apps
 417 APIs & Domo Developer
 27 Workflows
 1 DomoAI
 28 Predict
 12 Jupyter Workspaces
 16 R & Python Tiles
 361 Distribute
 99 Domo Everywhere
 260 Scheduled Reports
 2 Software Integrations
 96 Manage
 93 Governance & Security
 15 Product Releases
 Community Forums
 37 Getting Started
 28 Community Member Introductions
 90 Community Announcements
 4.8K Archive