Beast Mode Filter

Options
carthur
carthur Member
edited July 7 in Calculations & Variables (Beast Mode)

Hello All,

I have these 2 Beast Mode calc and I am trying to understand why it is not filtering from.

(2022-01-01) to (today).

image.png

What am I missing here please?

And with this below too why am I getting error. Any help please?

image.png

I just want to filter dates from start of previous year to today of last year.

2022-01-01 to same day today of last (2022-11-06)

thank you all.

Best Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @carthur On your second formula, you are getting an error because the logic needs to be within a case statement. I'd suggest doing this instead:

    case 
  when YEAR(`ORDER_DATE`) = YEAR(DATE_SUB(CURDATE(),interval 1 year)) 
    and ORDER_DATE <= DATE_SUB(CURDATE(),interval 1 year)
  then 'Include' 
  else 'Exclude'
end
  • GrantSmith
    GrantSmith Coach
    edited November 2023 Answer ✓ 
    CURRENT_DATE() - INTERVAL (DAYOFYEAR(`ORDER_DATE`)-1) DAY - INTERVAL 1 YEAR
    • DAYOFYEAR - Returns the day number of the year of the given date, ORDER_DATE in your example. (Jan 1 = 1, Dec 31 = 365/366)
    • - INTERVAL (DAYOFYEAR(`ORDER_DATE`)-1) DAY - Subtract the number of days the current date is minus one so we get to day 1 of the year (Jan 1st)
    • - INTERVAL 1 YEAR - Subtract an entire year so you'll get January 1st of the prior year

    -INTERVAL is the same as DATE_SUB just a different format.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    edited November 2023

    Your STR_TO_DATE format is not quite correct.

    The first one isn't including the dashes that you've concatenated in there. The second is including commans which aren't included.

    Try something like this:

    CASE WHEN `ORDER_DATE` >= STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE()-1), '-01-01'), '%Y-%m-%d')
AND 
`ORDER_DATE` <= STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE())-1, '-', MONTH(CURRENT_DATE()), '-', DAY(CURRENT_DATE())), '%Y-%m-%d')
THEN 'YES'
ELSE 'NO'
END
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • GrantSmith
    GrantSmith Coach
    edited November 2023

    You can also simplify the conditions to:

    CASE WHEN `ORDER_DATE` >= CURRENT_DATE() - INTERVAL (DAYOFYEAR(`ORDER_DATE`)-1) DAY - INTERVAL 1 YEAR
AND 
`ORDER_DATE` <= CURRENT_DATE() - INTERVAL 1 YEAR
THEN 'YES'
ELSE 'NO'
END
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MichelleH
    MichelleH Coach
    Answer ✓

    @carthur On your second formula, you are getting an error because the logic needs to be within a case statement. I'd suggest doing this instead:

    case 
  when YEAR(`ORDER_DATE`) = YEAR(DATE_SUB(CURDATE(),interval 1 year)) 
    and ORDER_DATE <= DATE_SUB(CURDATE(),interval 1 year)
  then 'Include' 
  else 'Exclude'
end
  • carthur
    carthur Member

    Thank you both.

    @GrantSmith your second option didn't work but very interesting calculation, would you please explain that concept a little for me?

  • GrantSmith
    GrantSmith Coach
    edited November 2023 Answer ✓ 
    CURRENT_DATE() - INTERVAL (DAYOFYEAR(`ORDER_DATE`)-1) DAY - INTERVAL 1 YEAR
    • DAYOFYEAR - Returns the day number of the year of the given date, ORDER_DATE in your example. (Jan 1 = 1, Dec 31 = 365/366)
    • - INTERVAL (DAYOFYEAR(`ORDER_DATE`)-1) DAY - Subtract the number of days the current date is minus one so we get to day 1 of the year (Jan 1st)
    • - INTERVAL 1 YEAR - Subtract an entire year so you'll get January 1st of the prior year

    -INTERVAL is the same as DATE_SUB just a different format.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories