Beastmode Error - An issue has occurred during processing.
I am trying to create a simple beastmode to calculate the last week vs 6 week weekly average variance with the calculation below and I keep getting this error message. I've tried troubleshooting but I can't figure out why this isn't working. Can someone help me look into this?
CASE WHEN SUM(CASE WHEN Year Scenario Rolling
= 'This Year' AND Fiscal Week Index
= (Current Week Index
- 1) THEN IFNULL(Orders
,0) ELSE 0 END)>0 THEN
(SUM(CASE WHEN Year Scenario Rolling
= 'This Year' AND Fiscal Week Index
= (Current Week Index
- 1) THEN IFNULL(Amount_Sales
,0) ELSE 0 END)
/
SUM(CASE WHEN Year Scenario Rolling
= 'This Year' AND Fiscal Week Index
= (Current Week Index
- 1) THEN IFNULL(Orders
,0) ELSE 0 END))
/
(CASE WHEN SUM(CASE WHEN Year Scenario Rolling
= 'This Year' AND Fiscal Week Index
> (Current Week Index
- 8) AND Fiscal Week Index
< (Current Week Index
- 1) THEN IFNULL(Orders
,0) ELSE 0 END)>0 THEN
(SUM(CASE WHEN Year Scenario Rolling
= 'This Year' AND Fiscal Week Index
> (Current Week Index
- 8) AND Fiscal Week Index
< (Current Week Index
- 1) THEN IFNULL(Amount_Sales
,0) ELSE 0 END)
/
SUM(CASE WHEN Year Scenario Rolling
= 'This Year' AND Fiscal Week Index
> (Current Week Index
- 8) AND Fiscal Week Index
< (Current Week Index
- 1) THEN IFNULL(Orders
,0) ELSE 0 END)) ELSE 0 END) - 1 END
Best Answers
-
I agree with @marcel_luthi that the issue is likely caused by the nested aggregation and that it would be helpful to see a sample of the data. I'd also suggest summarizing the logic you used to create the beast mode in words so we can point you in the right direction.
3 -
@MichelleH said what I was going to say. It can't handle aggregates of aggregates. I find that putting the sum outside the case logic often solves this issue. Think of your case statement as a filter, and then aggregate the results.
I also recommend breaking it apart and reviewing the results of each portion of the beast mode to see where you may need to rearrange the logic. It's basically algebra, so you need to figure out the basic components and and arrange them so that the order of operations is clear and as concise as possible.
DataMaven
Breaking Down Silos - Building Bridges
**Say "Thanks" by clicking a reaction in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Answers
-
Hmm…
• Make sure all parentheses are properly closed and all function names are correctly spelled.
• Make sure the data fields you're referencing exist in your dataset and are named correctly.
• Make sure Current Week Index is a valid reference.
• Division by Zero: Make sure denominators are not zero, as division by zero is undefined. This could happen if there are no orders in the specified weeks. Possible use of coalesce when conditions are null.
• Check if the conditions used in your SUM functions are correctly filtering the data for the weeks you're using.** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **1 -
What is the error message you are getting? Can you share mock data with the columns you have and what the calculation would be if you perform it manually? The reason I'm asking is that you have nested aggregation functions (SUM of a SUM) without any window clause (OVER or FIXED) which is odd, so perhaps the formula could be simplified once we understand what the desired calculation is explained in words with an example.
3 -
I agree with @marcel_luthi that the issue is likely caused by the nested aggregation and that it would be helpful to see a sample of the data. I'd also suggest summarizing the logic you used to create the beast mode in words so we can point you in the right direction.
3 -
@MichelleH said what I was going to say. It can't handle aggregates of aggregates. I find that putting the sum outside the case logic often solves this issue. Think of your case statement as a filter, and then aggregate the results.
I also recommend breaking it apart and reviewing the results of each portion of the beast mode to see where you may need to rearrange the logic. It's basically algebra, so you need to figure out the basic components and and arrange them so that the order of operations is clear and as concise as possible.
DataMaven
Breaking Down Silos - Building Bridges
**Say "Thanks" by clicking a reaction in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
I opened the card today and the beastmode is now working on the card. I'm not sure why it wasn't working last week, but I appreciate all of your input! Thank you!
1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 49 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive