YoY Beast Mode Issue - "Issue Occurred While Processing"

user07803
user07803 Member
in Archive

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

  • zcameron
    zcameron Contributor

    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
    PodiumMason Contributor

    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'
  • user07803
    user07803 Member

    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
    zcameron Contributor

    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!

Categories