Help with beast mode formula - "case when" with date ranges

cbreen

Hello!

A little context about the use of this beast mode in the card:

• Looking at YOY data with same date ranges
• Date Ranges: 8/11-10/10

Here is the formula I have created:

case when month(`date_purchased`) = 8 and DAYOFMONTH(`date_purchased`) >= 11 then 1 else 

case when month(`date_purchased`) = 10 and DAYOFMONTH(`date_purchased`) <= 10 then 1 else 0 end end

Seems simple? I believe I am missing "September" or 9 in my formula but when I do try and add it in, the formula is invalid. Any help would be greatly appreciated! Thank you - Carly 🙂

RobSomers

@cbreen You'll want to modify it to the following:

case when month(`date_purchased`) = 8 and DAYOFMONTH(`date_purchased`) >= 11 then ( 

case when month(`date_purchased`) = 10 and DAYOFMONTH(`date_purchased`) <= 10 then 1 else 0) else 0 end

This will say if the date is after 8/11 then look at if it's before 10/10 and if it is then 1 else 0, but if it's before 8/11 then also 0. Since you're wanting to evaluate the second condition only if the date is after 8/11, then you need to nest the second condition in parantheses.

MichelleH

Hi @cbreen, a couple edits to your formula:

• When you have multiple conditions you need to check, you can simply contain them with a single CASE...END. Instead of repeating "CASE", you can specify each new condition with WHEN
• You are correct that your current formula does not account for September. You can add this as an additional condition

Try something like this:

case 
when month(`date_purchased`) = 8 and DAYOFMONTH(`date_purchased`) >= 11 then 1
when month(`date_purchased`) = 9  then 1
when month(`date_purchased`) = 10 and DAYOFMONTH(`date_purchased`) <= 10 then 1
else 0 
end 

cbreen

Thank you both @MichelleH & @RobSomers! Both explanations are great and help me understand how these formulas work - will try both in my cards. I appreciate the help! 🙂