Skip to content

    • Magic ETL

    Magic ETL

    Summing Up Last 12 Weeks Beast Mode

    Member
    in Magic ETL

    Hi There,

     

    I am trying to do write a beast mode to sum up Revenue from the Last 12 weeks but not counting the previous week. for example

     

    Last Week = 11/22 - 11/28 (Sunday - Saturday)

     

    I want to go back 12 weeks from last week, so I would start from the Week of 11/15 

     

    This is what I've come up with but it doesn't seem to return the correct value

     

    SUM(CASE WHEN (`Date`) > DATE_SUB(LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK)), INTERVAL 14 WEEK)
    AND (`Date`) <=
    LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK))
    THEN `Revenue`

    END)

     

    Thanks!

     

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answers

    • Coach
      Answer ✓

      Hi @user32470 

       

      How is the returned value not correct? Is it too high? Too low?

       

      Looking at your query it appears that you're using the LAST_DAY function which returns the last day of the month and not the last day of the week.

       

      Try something like this:

      1. CASE WHEN `dt` >= DATE_SUB(CURRENT_DATE(), DAYOFWEEK(CURRENT_DATE()) + (7*12)) 
      2. 	  AND `dt` <= DATE_SUB(CURRENT_DATE(), DAYOFWEEK(CURRENT_DATE()) + (0*7))
      3. THEN 1
      4. ELSE 0
      5. END

       

      This ENDs at the end of last week and starts 12 weeks before the prior week. If necessary you can increase your multiplication factors by 1 to move back a week so it'd be 13-2 instead of 12-1

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

      Thanks, Grant!

       

      Wasn't aware that Last Day was for month - I used that beast mode for a "Last 12 months" and thought I could just easily modify with "Weeks" ? good to know

       

      So I tried out your query (shifted to 13-2) and the value that's being returned is slightly higher than what's being returned via the query. I attached a screengrab but when I am manually summing it up in excel, I get $20,009. Through the beast mode, I am getting $20,148. For some reason it's including the value from the single day of August 29th, 2020. I can't seem to figure out why it's doing that....

    • Coach
      Answer ✓

      Minor tweak needed for the beast mode (just need to use > instead of >= - sorry!)

       

       

       

       

      1. SUM(CASE WHEN `Date` > DATE_SUB(CURRENT_DATE(), DAYOFWEEK(CURRENT_DATE()) + (13*7)) 
      2. 	  AND `Date` <= DATE_SUB(CURRENT_DATE(), DAYOFWEEK(CURRENT_DATE()) + (1*7))
      3. THEN `Revenue`
      4. ELSE 0
      5. END)

       

       

       

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

    Answers

    • Coach
      Answer ✓

      Hi @user32470 

       

      How is the returned value not correct? Is it too high? Too low?

       

      Looking at your query it appears that you're using the LAST_DAY function which returns the last day of the month and not the last day of the week.

       

      Try something like this:

      1. CASE WHEN `dt` >= DATE_SUB(CURRENT_DATE(), DAYOFWEEK(CURRENT_DATE()) + (7*12)) 
      2. 	  AND `dt` <= DATE_SUB(CURRENT_DATE(), DAYOFWEEK(CURRENT_DATE()) + (0*7))
      3. THEN 1
      4. ELSE 0
      5. END

       

      This ENDs at the end of last week and starts 12 weeks before the prior week. If necessary you can increase your multiplication factors by 1 to move back a week so it'd be 13-2 instead of 12-1

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

      Thanks, Grant!

       

      Wasn't aware that Last Day was for month - I used that beast mode for a "Last 12 months" and thought I could just easily modify with "Weeks" ? good to know

       

      So I tried out your query (shifted to 13-2) and the value that's being returned is slightly higher than what's being returned via the query. I attached a screengrab but when I am manually summing it up in excel, I get $20,009. Through the beast mode, I am getting $20,148. For some reason it's including the value from the single day of August 29th, 2020. I can't seem to figure out why it's doing that....

    • Coach
      Answer ✓

      Minor tweak needed for the beast mode (just need to use > instead of >= - sorry!)

       

       

       

       

      1. SUM(CASE WHEN `Date` > DATE_SUB(CURRENT_DATE(), DAYOFWEEK(CURRENT_DATE()) + (13*7)) 
      2. 	  AND `Date` <= DATE_SUB(CURRENT_DATE(), DAYOFWEEK(CURRENT_DATE()) + (1*7))
      3. THEN `Revenue`
      4. ELSE 0
      5. END)

       

       

       

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

      That makes sense now - so we want to look at the date after not the "day". it adds up now. thanks so much!!

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories