SUM of CASE in Beast Mode

Hello,

I would like to create a beast mode which can give me a % of a column in a table (e.g. % OCJ Actual) like such:

Marketing IntentFY GoalOCJ Actual% OCJ Actual
COLLABORATION5,000,000500,00010%
SECURITY5,000,000500,00010%

 

The beast mode formula seems correct but I am getting this error 'An issue has occurred during processing. We are unable to complete the request at this time.' when I put the beast mode as a column in the table. Can anyone help please? TIA

  

My beast mode is as follow:

(
CASE when `Business Entity` = 'COLLABORATION' then
(SUM((CASE WHEN `Business Entity` IN ('COLLABORATION') AND `Activity Quarter` = '2019Q1' THEN `Engagement Points` ELSE 0 END ))) /
(SUM((CASE when `Business Entity` IN ('COLLABORATION') then `Engagement Points` else 0 end)))

 

when `Business Entity` = 'SECURITY' then
(SUM((CASE WHEN `Business Entity` IN ('SECURITY') AND `Activity Quarter` = '2019Q1' THEN `Engagement Points` ELSE 0 END ))) /
(SUM((CASE when `Business Entity` IN ('SECURITY') then `Engagement Points` else 0 end)))

end)

 

Comments

  • This seems perhaps overly complicated. Collaboration and Security appear to have the same calculations, so I think you can skip the outer CASE.

     

    SUM(CASE WHEN `Activity Quarter` = '2019Q1' THEN `Engagement Points` END)

    /

    SUM(`Engagement Points`)

     

    Also, is Activity Quarter a date datatype or a string?  That would make a difference in how you make that comparison.

  • I might try something like this:

     

    CASE WHEN SUM(`Engagement Points`)=0 then 0 ELSE

    SUM(CASE WHEN `Activity Quarter` = '2019Q1' THEN `Engagement Points` ELSE 0 END)

    /

    SUM(`Engagement Points`) END

     

    The additions that I made should just help clean up any sloppy data.  The outer case statement prevents the beastmode from dividing by 0 and the "ELSE 0" on the inner case statement will cause the beastmode to still calculate even when the `Activity Quarter` is not '2019Q1'.