Beast Mode Filter

carthur

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

What am I missing here please?

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

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.

MichelleH

@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

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.

GrantSmith

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

GrantSmith

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

MichelleH

@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

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

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.