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

cbreen
cbreen Member
edited March 2023 in Beast Mode

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 🙂

Tagged:

Answers

  • RobSomers
    RobSomers Coach
    edited August 2022

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

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • MichelleH
    MichelleH Coach

    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
    cbreen Member

    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! 🙂

Categories