Pulling latest 4 weeks from Max date in Beastmode

user17569
user17569 Member
in Magic ETL

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!

Best Answer

  • user17569
    user17569 Member
    Answer ✓

    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'

Answers

  • guitarhero23
    guitarhero23 Contributor

    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.**
  • user17569
    user17569 Member
    Answer ✓

    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'

Categories