YoY Beast Mode Issue - "Issue Occurred While Processing"
Hello,
I'm relatively new to Beast Mode within DOMO and am running into issues trying to build a year-over-year calculation. I've built the below script, which was accepted by Beast Mode but is now giving me a "an issue has occured during processing" error. Any ideas on how I could fix this?
CASE WHEN `Actual Revenue` = 0 THEN 0
ELSE
(sum(case
when year(`Line Item Date`) = year(curdate())
then `Actual Revenue`else 0 end)
-
sum(case
when year(`Line Item Date`) = year(curdate())-1 and week(`Line Item Date`) <= week(curdate())
then `Actual Revenue` else 0 end))
/
(sum(case
when year(`Line Item Date`) = year(curdate())-1 and week(`Line Item Date`) <= week(curdate())
then `Actual Revenue` else 0 end))
END
Comments
-
The trouble you're seeing may be due to a combination of different levels of granularity in the beast mode calculation. The first line in the code is looking at a single record in the dataset, but lines below it are looking at a group of records being aggregated together using a SUM function.
Try wrapping a SUM around your `Actual Revenue` on line 1 to keep it at the same level of granularity.
Let us know if that helps!
1 -
Hey @user07803,
At a glance, the issue with your beast mode is the initial CASE statement.
CASE WHEN `Actual Revenue` = 0 THEN 0
Beast mode gets pretty disagreeable when you try to mix row level calcs (non aggregated) with set level calcs (aggregated).
You could add the first beast mode as a component of each of the other case statements. For example:
SUM(Case when `Actual Revenue` = 0 then 0
when year(`Line Item Date`) = year(curdate()) then `Actual Revenue`
else 0 end)
See if this gets you anywhere.
Best of luck!
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'1 -
Thanks. I implemented your script and am now getting a divide by zero error.
(SUM (Case when `Actual Revenue` = 0 then 0
when year(`Line Item Date`) = year(curdate()) then `Actual Revenue`
else 0 end)
-
SUM (Case when `Actual Revenue` = 0 then 0
when year(`Line Item Date`) = year(curdate())-1 then `Actual Revenue`
else 0 end))
/
SUM (Case when `Actual Revenue` = 0 then 0
when year(`Line Item Date`) = year(curdate())-1 then `Actual Revenue`
else 0 end)0 -
You just need to add a check for situations where your denomintor may be 0 or null. For example you could modify your code like this:
CASE
WHEN SUM (Case when `Actual Revenue` = 0 then 0
when year(`Line Item Date`) = year(curdate())-1 then `Actual Revenue`
else 0 end) > 0 THEN(SUM (Case when `Actual Revenue` = 0 then 0
when year(`Line Item Date`) = year(curdate()) then `Actual Revenue`
else 0 end)
-
SUM (Case when `Actual Revenue` = 0 then 0
when year(`Line Item Date`) = year(curdate())-1 then `Actual Revenue`
else 0 end))
/
SUM (Case when `Actual Revenue` = 0 then 0
when year(`Line Item Date`) = year(curdate())-1 then `Actual Revenue`
else 0 end)END
This would only run the calculation when the denominator is > 0. Otherwise it will return a null value. If you'd rather have it return a 0 or some other number, just add an ELSE clause before the final end.
I hope that helps!
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
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 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