Rolling Count Current Week

I am trying to create a table with rolling count of the current week, last 4 weeks, and last 8 weeks. I was able to create beast mode calculations for last 4 & 8 weeks, but I am lost in creating beast mode calculation for the current week. What would be the best way to calculate rolling count for the current week? Thank you in advance!
Below are the calculations for rolling 4 weeks and 8 weeks:
SUM(
CASE WHEN (`Date`) > DATE_SUB(CURRENT_DATE(), INTERVAL 4 WEEK)
AND (`Date`) <= CURRENT_DATE()
THEN `Units`
END)
SUM(
CASE WHEN (`Date`) > DATE_SUB(CURRENT_DATE(), INTERVAL 8 WEEK)
AND (`Date`) <= CURRENT_DATE()
THEN `Units`
END)
Best Answer
-
Hi @user069636
You can calculate the first day in a week using the DAYOFWEEK function in your beast mode.
SUM( CASE WHEN (`Date`) > DATE_SUB(CURRENT_DATE(), INTERVAL DAYOFWEEK(CURRENT_DATE()) DAY) AND (`Date`) <= CURRENT_DATE() THEN `Units` END)
DAYOFWEEK returns values between 1 (Sunday) and 7 (Saturday). Subtracting that value for the number of days will return the end of last week so you just need to check to see if the date is after the end of last week.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2
Answers
-
Hi @user069636
You can calculate the first day in a week using the DAYOFWEEK function in your beast mode.
SUM( CASE WHEN (`Date`) > DATE_SUB(CURRENT_DATE(), INTERVAL DAYOFWEEK(CURRENT_DATE()) DAY) AND (`Date`) <= CURRENT_DATE() THEN `Units` END)
DAYOFWEEK returns values between 1 (Sunday) and 7 (Saturday). Subtracting that value for the number of days will return the end of last week so you just need to check to see if the date is after the end of last week.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**2
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 656 Datasets
- 115 SQL DataFlows
- 2.2K Magic ETL
- 811 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 80 App Studio
- 45 Variables
- 771 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 77 Workflows
- 23 Code Engine
- 36 AI and Machine Learning
- 19 AI Chat
- AI Playground
- AI Projects and Models
- 17 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 142 Manage
- 138 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive