SUM of CASE in Beast Mode

user04693

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 Intent	FY Goal	OCJ Actual	% OCJ Actual
COLLABORATION	5,000,000	500,000	10%
SECURITY	5,000,000	500,000	10%

 

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)

 

AS

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.

ST_Superman

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'.