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!
Best Answers
-
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!**0 -
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....
0 -
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!**1
Answers
-
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!**0 -
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....
0 -
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!**1 -
That makes sense now - so we want to look at the date after not the "day". it adds up now. thanks so much!!
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 694 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive