Pulling latest 4 weeks from Max date in Beastmode

Reply
Highlighted
White Belt

Pulling latest 4 weeks from Max date in Beastmode

Hello All -

 

I'm trying to create a filter for the latest 4, 13, 26, and 52 weeks so I can pull sales by these time frames. I'm not trying to pull from the current date, but from the max date that is available because our reporting only comes in every 4 weeks. 

This is my current query that is only returning 'other'.

 

CASE WHEN DATE(`Date`) >= DATE(MAX(`Date`)) - INTERVAL 28 DAY THEN 'L4'
WHEN DATE(`Date`) >= DATE(MAX(`Date`)) - INTERVAL 91 DAY THEN 'L13'
WHEN DATE(`Date`) >= DATE(MAX(`Date`)) - INTERVAL 182 DAY THEN 'L26'
WHEN DATE(`Date`) >= DATE(MAX(`Date`)) - INTERVAL 364 DAY THEN 'L52'
ELSE 'OTHER'
END

 

Looking for help.

 

Thank you!


Accepted Solutions
White Belt

Re: Pulling latest 4 weeks from Max date in Beastmode

I actually found a solution using the rank & window function in Magic ETC then using CASE in MySQL.

 

I ranked the weeks in descending order in magic ETL which numbered the weeks in order from the most current week to the least current week. The I used the following case statements in mysql to get the latest 4 weeks for this year and last year.

 

CASE WHEN `Rank` BETWEEN 1 AND 4 THEN `SALES` ELSE 0 END AS 'L4 Current Year'

 

CASE WHEN 'RANK' BETWEEN 53 AND 56 THEN `SALES` ELSE 0 END AS 'L4 Prior Year'


All Replies
Brown Belt

Re: Pulling latest 4 weeks from Max date in Beastmode

Someone might be able to do this in beastmode without a dataflow so see if they come in with a good one, but you could do a dataflow that is just the MAX of date. Then since you have that value you don't have to inlcude it in the beastmode and just do something like

 

CASE 

WHEN DATEDIFF(`Date`, `Dataflow_MaxDate`) > 28 THEN 'L4'
WHEN DATEDIFF(`Date`, `Dataflow_MaxDate`) > 91 THEN 'L13'
WHEN DATEDIFF(`Date`, `Dataflow_MaxDate`) > 182 THEN 'L26'
WHEN DATEDIFF(`Date`, `Dataflow_MaxDate`) > 364 THEN 'L52'
ELSE 'OTHER'
END
White Belt

Re: Pulling latest 4 weeks from Max date in Beastmode

I actually found a solution using the rank & window function in Magic ETC then using CASE in MySQL.

 

I ranked the weeks in descending order in magic ETL which numbered the weeks in order from the most current week to the least current week. The I used the following case statements in mysql to get the latest 4 weeks for this year and last year.

 

CASE WHEN `Rank` BETWEEN 1 AND 4 THEN `SALES` ELSE 0 END AS 'L4 Current Year'

 

CASE WHEN 'RANK' BETWEEN 53 AND 56 THEN `SALES` ELSE 0 END AS 'L4 Prior Year'

Announcements
Looking for the latest Community solutions? Please visit our accepted solutions board here!