Summing Up Last 12 Weeks Beast Mode

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!

Find more posts tagged with

Accepted answers

GrantSmith

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

user32470

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....

Capture.PNG

GrantSmith

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)

All comments

GrantSmith

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

user32470

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

Capture.PNG

GrantSmith

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)

user32470

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