Pulling latest 4 weeks from Max date in Beastmode

Reply
Yellow 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
Yellow 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'

View solution in original post


All Replies
Major 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


**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Yellow 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'

View solution in original post

Announcements
Check out these great solution videos! The new iSolveditWithDomoboard is live, share yours and earn higher rankings in Dojo click here!