Multiple Case Statements
Hi Everyone,
I am having a bit of trouble stringing together multiple case statements...
The end goal of my attempted beast mode is to calculate Average Weekly Cost Per Lead (CPL is Cost divided by # Leads). The wrench in this whole scenario is that I have to do this based off the broadcast calendar. Broadcast Calendar is just a standardized calendar utilized in media buying/advertising world. Depending on the year (I am doing this for 2019 and 2020), each month will either have 4 or 5 weeks. I hope this makes sense
Below is the beast mode I built. Here is some context on what some of this stuff means
 Media Month is just the numerical value for each month (eg: 1 is January, 2 is Feb, 3 is March and so on).
 Media Year is the year
 Metric is just which metric I am telling the beast mode to get data from.
((sum(case when `MediaMonth` in ('1','2','4','6','7','9','10','11','12') and `MediaYear` = '2020' and `Metric` = 'Net Cost' then `Value` end)
/ sum(Case when `MediaMonth` in ('1','2','4','6','7','9','10','11','12') and `MediaYear` = '2020' and `Level` = 3 and `Metric` = 'Leads' then `Value` end))
/
4)
I was only successful in building this for the year 2020 for the months that only have 4 weeks in it. In the beast mode I have above, it's basically saying if the media month criteria is met for all the dimensions, then divide by 4 (those months have 4 weeks). This will get me my average weekly cost per lead. What I am missing are statements that will:
 Account for the months that have 5 weeks in 2020 (Months 3,5,8)
 Account for the same criteria (months with 4 and 5 weeks) for 2019
Anyone have any insight?
thanks!
Comments

Build a separate table, a date dimension, that has one row for every day since the dawn of time and then explicitly label the month and week number based on your requirements. Then JOIN that date table into all your dashboard datasets. It'll be MUCH easier than this CASE statement nightmare you're building for yourself!
What you're describing is not unique to Media. It's conceptually similar to a 445 calendar or a Retail calendar. There are many well documented blog posts about building a 445 calendar.
https://calogica.com/sql/dbt/2018/11/15/retailcalendarinsql.html
This article describes the problem and the sql solution reasonably well, you'll just have to refactor it for mySQL.
... i know it's sql intensive, but this will be much more supportable long term.
.... BUSINESS ANALYST QUESTIONS ...
are you trying to calculate TotalCost / TotalLeads?
or are you trying to calculate ( avgCPL month 1 + avgCPL month 2 + avgCPL month 3 ... ) / 12
I don't know if it's appropriate to take the average of an average... but if you're interested in the first one then you can ignore the convolution of weeks per month.
if you want to do avg of avg cpl by month, then you do have to consider the month the activity occurred in, but it doesn't sound like it should be impacted by number of weeks per month.
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"0
Categories
 10.6K All Categories
 1 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
 339 Workbench
 252 Domo Best Practices
 11 Domo Certification
 461 Domo Developer
 47 Domo Everywhere
 100 Apps
 703 New to Domo
 84 Dojo
 Domopalooza
 1.1K 日本支部
 4 道場日本支部へようこそ
 22 お知らせ
 63 Kowaza
 296 仲間に相談
 649 ひらめき共有