Beast Mode Case Statement Pivot Table issue

BradRussell
BradRussell Member
in Magic ETL

I have a Beast Mode calculation that has a case statement that looks to pull actual data from the past and projected data from the future.  Everything works fine in the pivot table card with the exception of the total.  It appears that the case statement is affecting the total instead of the pivot table just summing the individual values.  Is this a bug or expected behavior?  Any suggesting for solving or a work around would be much appreciated!

 

Below is the formula and the result.

 

(CASE
WHEN `IMPORT_CLOSE_DATE`<= CURRENT_DATE() then sum(`Pacing_Revenue_Forecast GROSS`)
ELSE (sum(`CY On the Books`)/ sum(`LY On the Books`)* SUM(`LY Finish`-`LY On the Books`))+ (sum(`CY On the Books`))
END)

 

 

Pivot Table.PNG

Comments

  • MichelleH
    MichelleH Coach

    Hi @user19682, 

     

    This behavior is to be expected when using aggregate functions in beast modes. It's not a bug since it's actually beneficial for certain calculations, such as percentages or ratios, where adding up the individual values is not necessarily meaningful.

     

    With that in mind, I would suggest using a dataflow to group all your data by month, then creating the same calculation for Proj Fcst but without using SUM functions. That way you can completely avoid using aggregate functions in a beast modes and let the total row use a simple sum rather than it applying the formula to your total as a whole. 

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    It's behaving as designed.

     

    because your CASE statement is OUTSIDE of the SUM, it applies the CASE AFTER aggregation has happened, or to use your language "the case statement is affecting the total instead of the ... the individual values. "

     

    To get your total to work as expected you have to put the CASE statement INSIDE of the aggregation.

    SUM(CASE ...)

     

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories