Can someone help me with this Beast Mode using CASE Statement using FIXED(BY) Formula?
I am trying to create a beast mode that looks at different criteria to only sum certain data. I have tried multiple ways to write this, but I cannot get it to work the way I want it to.
If I write it this way, the formula works however it only displays the data from the else portion and ignores the other criteria.
CASE WHEN Coop Event = 'Aug 2024 Moto Coop 2' THEN
SUM(CASE WHEN SUM(OrderGrandTotal) FIXED (BY MpowerOrder) < 963 THEN 0 ELSE SUM(OrderGrandTotal) FIXED (BY MpowerOrder)963 END)
WHEN Coop Event = 'Aug 2024 Moto Coop 1' THEN
SUM(CASE WHEN SUM(OrderGrandTotal) FIXED (BY MpowerOrder) < 463 THEN 0 ELSE SUM(OrderGrandTotal) FIXED (BY MpowerOrder)463 END)
ELSE SUM(SUM(OrderGrandTotal) FIXED (BY MpowerOrder))
END
If I write it his way which makes sense to me the formula validates correct but it gives me this error which is not accurate.
An invalid column was specified in your request (the column does not exist on the DataSet).
all of my columns are valid. I am hoping someone can help me to determine how to correctly write this.
SUM(CASE WHEN (Coop Event = 'Aug 2024 Moto Coop 2' AND SUM(OrderGrandTotal) FIXED (BY MpowerOrder) >= 963) THEN SUM(OrderGrandTotal) FIXED (BY MpowerOrder)963
WHEN (Coop Event = 'Aug 2024 Moto Coop 1' AND SUM(OrderGrandTotal) FIXED (BY MpowerOrder) >= 463) THEN SUM(OrderGrandTotal) FIXED (BY MpowerOrder)463
WHEN (Coop Event = 'May 2024 Coop') THEN SUM(OrderGrandTotal) FIXED (BY MpowerOrder)
ELSE 0
END)
Best Answer

Can you process this through a Magic ETL?
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Answers

It appears you may be having trouble because of the nested aggregation (SUM within a SUM). Try this:
CASE
WHEN Coop Event = 'Aug 2024 Moto Coop 2' THEN
SUM(CASE WHEN OrderGrandTotal >= 963 THEN OrderGrandTotal  963 ELSE 0 END) FIXED (BY MpowerOrder)
WHEN Coop Event = 'Aug 2024 Moto Coop 1' THEN
SUM(CASE WHEN OrderGrandTotal >= 463 THEN OrderGrandTotal  463 ELSE 0 END) FIXED (BY MpowerOrder)
WHEN Coop Event = 'May 2024 Coop' THEN
SUM(OrderGrandTotal) FIXED (BY MpowerOrder)
ELSE
0
END** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 
Thanks for the quick response. I tried your suggestion and again the formula validated ok but the card is displaying this error.
0 
You could try to "debug" the issue by simplifying and testing aggregation separately
SUM(OrderGrandTotal) FIXED (BY MpowerOrder)
And testing the logic without the aggregation:
CASE
WHEN Coop Event = 'Aug 2024 Moto Coop 2' THEN
CASE
WHEN OrderGrandTotal >= 963 THEN OrderGrandTotal  963
ELSE 0
END
WHEN Coop Event = 'Aug 2024 Moto Coop 1' THEN
CASE
WHEN OrderGrandTotal >= 463 THEN OrderGrandTotal  463
ELSE 0
END
ELSE OrderGrandTotal
ENDThis will help you validate the logic before aggregating. You might also consider occurrences of null values. Using coalesce to convert null values to zero to see if you have data gaps disrupting the calculations.
COALESCE(OrderGrandTotal, 0)
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 
so I tried both the aggregation and tested the logic without aggregation as you mentioned above. they both validated and produced a value but not the value I was looking for. I also validated there are no NULL values in the data but still tested your suggestion.
For some background, the way the data is arranged is each 'MpowerOrder' number is broken out into multiple rows for each location on the order. I am looking to see if each overall 'MpowerOrder' total is more than either 963 or 463 depending on the 'Coop Event' field. this is why I am using the FIXED BY in my formula so I can get the total order amount. I believe this is what is making it most difficult.
Any further suggestions you might have would be greatly appreciated.
0 
How about doing it this way?
CASE
WHEN Coop Event = 'Aug 2024 Moto Coop 2' THEN
SUM(CASE
WHEN SUM(OrderGrandTotal) FIXED (BY MpowerOrder) >= 963 THEN SUM(OrderGrandTotal) FIXED (BY MpowerOrder)  963
ELSE 0
END) FIXED (BY MpowerOrder)
WHEN Coop Event = 'Aug 2024 Moto Coop 1' THEN
SUM(CASE
WHEN SUM(OrderGrandTotal) FIXED (BY MpowerOrder) >= 463 THEN SUM(OrderGrandTotal) FIXED (BY MpowerOrder)  463
ELSE 0
END) FIXED (BY MpowerOrder)
ELSE SUM(OrderGrandTotal) FIXED (BY MpowerOrder)
END** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 
I appreciate it ArborRose but this one also gives the below error. I am wondering if the calculation is just too much for Beast Mode to handle.
0 
Can you process this through a Magic ETL?
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 
yes I was thinking I may need to try it that way next. thanks for your help.
0
Categories
 All Categories
 1.8K Product Ideas
 1.8K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 296 Workbench
 6 Cloud Amplifier
 8 Federated
 2.9K Transform
 99 SQL DataFlows
 614 Datasets
 2.2K Magic ETL
 3.8K Visualize
 2.5K Charting
 727 Beast Mode
 53 App Studio
 40 Variables
 677 Automate
 173 Apps
 451 APIs & Domo Developer
 45 Workflows
 8 DomoAI
 34 Predict
 14 Jupyter Workspaces
 20 R & Python Tiles
 394 Distribute
 113 Domo Everywhere
 275 Scheduled Reports
 6 Software Integrations
 121 Manage
 118 Governance & Security
 Domo Community Gallery
 32 Product Releases
 10 Domo University
 5.4K Community Forums
 40 Getting Started
 30 Community Member Introductions
 108 Community Announcements
 4.8K Archive