Sum Previous 4 Weeks of Sales Together
Hi,
I'm trying to sum our weekly sales data into 4 weeks of sales. For example, if we had $10 in sales each of the past 4 weeks, the value returnded should be $40.
I'm using this function:
SUM(CASE
WHEN WEEK('Week Beginning') > WEEK('Week Beginning') - 4
THEN 'Sales'
ELSE 0
END)
But this is just returning the sales for last week, not the aggregate of the past 4 weeks.
Thank you
Comments
-
We may need to see a sample of columns and values.
That being said, try putting a MAX function in front of the second WEEK statement. This way, you're taking all the weeks which are within 4 weeks of the most current week.
At some point, you'll need to aggregate the Sales, but perhaps you're doing that in the output. If it's not working try summing the THEN.
DataMaven
Breaking Down Silos - Building Bridges
**Say "Thanks" by clicking a reaction in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
Hi
The Max Function would not work in this case
Try using 'Today' instead of 'Week Beginning' on the second instance.
Also try addign YEAR() to the beastmode otherwise the logic will break during the first 3 weeks of the year
1 -
Thanks All! I'm going to play around with your suggestions and see how it turns out.
1 -
Good thinking @rado98 on those first few weeks!
@user17569 - Make sure to let us know what works, and if there's anything we can add.
DataMaven
Breaking Down Silos - Building Bridges
**Say "Thanks" by clicking a reaction in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
- 10.6K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 477 Transform
- 1.8K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 218 Visualize
- 260 Beast Mode
- 2.1K Charting
- 12 Variables
- 19 Automate
- 356 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 249 Distribute
- 65 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 191 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive