YoY Beast Mode Issue - "Issue Occurred While Processing"

user07803

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

zcameron

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!

PodiumMason

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!

 

 

                  

 

user07803

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)

zcameron

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!