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
- All Categories
- 1.2K Product Ideas
- 1.2K Ideas Exchange
- 1.3K Connect
- 1.1K Connectors
- 273 Workbench
- 2 Cloud Amplifier
- 3 Federated
- 2.7K Transform
- 78 SQL DataFlows
- 525 Datasets
- 2.1K Magic ETL
- 2.9K Visualize
- 2.2K Charting
- 434 Beast Mode
- 22 Variables
- 511 Automate
- 114 Apps
- 389 APIs & Domo Developer
- 8 Workflows
- 26 Predict
- 10 Jupyter Workspaces
- 16 R & Python Tiles
- 332 Distribute
- 77 Domo Everywhere
- 255 Scheduled Reports
- 66 Manage
- 66 Governance & Security
- 1 Product Release Questions
- Community Forums
- 40 Getting Started
- 26 Community Member Introductions
- 68 Community Announcements
- 4.8K Archive