Summing Up Last 12 Weeks Beast Mode

Options
user32470
user32470 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!

 

Best Answers

  • GrantSmith
    GrantSmith 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:

    CASE WHEN `dt` >= DATE_SUB(CURRENT_DATE(), DAYOFWEEK(CURRENT_DATE()) + (7*12)) 
	  AND `dt` <= DATE_SUB(CURRENT_DATE(), DAYOFWEEK(CURRENT_DATE()) + (0*7))
THEN 1
ELSE 0
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!**
  • user32470
    user32470 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....

  • GrantSmith
    GrantSmith Coach
    Answer ✓

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

     

     

     

     

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

     

     

     

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

Answers

  • GrantSmith
    GrantSmith 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:

    CASE WHEN `dt` >= DATE_SUB(CURRENT_DATE(), DAYOFWEEK(CURRENT_DATE()) + (7*12)) 
	  AND `dt` <= DATE_SUB(CURRENT_DATE(), DAYOFWEEK(CURRENT_DATE()) + (0*7))
THEN 1
ELSE 0
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!**
  • user32470
    user32470 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....

  • GrantSmith
    GrantSmith Coach
    Answer ✓

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

     

     

     

     

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

     

     

     

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

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

Categories