Beast mode with multiple case statements

soulless

Hi,

I would really appreciate help with a beast mode that I am unable to resolve even after looking into many answers on dojo. The beast mode looks like below:

CASE when

  (CASE when `Cost Type` like 'Fix Price' then 

   (CASE when `End Date` < CURRENT_DATE-3 then `CPM`

   else (`CPM`/(DATEDIFF(`End Date`,`Start Date`)+1))*(DATEDIFF(MAX(`Date`),`Start Date`)+1) end)

   when `Cost Type` like 'CPC' and SUM(`Clicks`) > 0 then SUM(`Clicks`)*`CPM` 

   when `Cost Type` like 'CPM' then SUM((`Impressions`)/1000)*`CPM`else 0 end) = 0

  then 0 

else SUM(`VT`)/(CASE when `Cost Type` like 'Fix Price' then 

   (CASE when `End Date` < CURRENT_DATE-3 then `CPM`

   else (`CPM`/(DATEDIFF(`End Date`,`Start Date`)+1))*(DATEDIFF(MAX(`Date`),`Start Date`)+1) end)

   when `Cost Type` like 'CPC' and SUM(`Clicks`) > 0 then SUM(`Clicks`)*`CPM` 

   when `Cost Type` like 'CPM' then SUM((`Impressions`)/1000)*`CPM`

  else 0 end)

end

It should:

• show division of two numbers: SUM of VT / SUM of another calculated summarized value based on multiple conditions
• to avoid error when dividing by zero, I first check that the calculated value is not 0

However what it results in instead us SUM of multiple calculations (VT / Fix price + VT / CPC + VT/CPM).

I want to display the result as a single value chart type.

What am I doing wrong here?

GrantSmith

Hi @soulless

It's a bit tricky as you're attempting to do both a SUM and a MAX inside your case statements. You can try and rewrite it so that your conditions are inside your aggregate. This is a bit tricky because for your Fix Price is attempting to use MAX but your others are using SUM. To get around this you can try to have two separate aggregates and add them together:

SUM(`VT`)
/
(MAX(CASE when `Cost Type` like 'Fix Price' then

  (CASE when `End Date` < CURRENT_DATE-3 then `CPM`

  else (`CPM`/(DATEDIFF(`End Date`,`Start Date`)+1))*(DATEDIFF(`Date`,`Start Date`)+1) end)
  else 0
END)
+
  SUM(CASE when `Cost Type` like 'CPC' then `Clicks`

  when `Cost Type` like 'CPM' then `Impressions`

  else 0 end) * `CPM` / (CASE WHEN `Cost Type` like 'CPM' then 1000 ELSE 1 END)
)

This is the simplified version without checking the denominator for 0 for simpler readability but you can add that in easily.

This is all back of the napkin coding and I haven't tested any of it but it should help highlight the idea.

Another thing I noticed about your beast mode when you're using like 'Fix Price' it's the same as saying `Cost Type` = 'Fix Price'. Are you wanting this behavior or are you checking to see if the Cost Type contains Fix Price? In that case you'd need to do `Cost Type` LIKE '%Fix Price%' - same for the other uses in your Beast Mode.