Beast mode with multiple case statements
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_DATE3 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_DATE3 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?
Answers

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_DATE3 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.
2
Categories
 10.6K All Categories
 APAC User Group
 12 Welcome
 36 Domo News
 9.6K Using Domo
 1.9K Dataflows
 2.4K Card Building
 2.2K Ideas Exchange
 1.2K Connectors
 337 Workbench
 250 Domo Best Practices
 11 Domo Certification
 460 Domo Developer
 47 Domo Everywhere
 100 Apps
 703 New to Domo
 83 Dojo
 Domopalooza
 1.1K 日本支部
 4 道場日本支部へようこそ
 22 お知らせ
 62 Kowaza
 295 仲間に相談
 649 ひらめき共有