Last 4 weeks accumulated beast mode
Hello,
I'm trying to write a beast modes that shows the last 4 weeks as Last Week, the previous 4 weeks before that as Previous Week, and the last 4 weeks prior year as Prior Year.
The reason why I will create 3 beast modes is because only want 3 columns showing for these accumulated week periods, versus showing 52 columns total (weekly column)
The follow is a beast mode for last week:
SUM(CASE WHEN (
DATE((CASE WHEN DAYOFWEEK(`SentDate`) = 1 THEN `SentDate`
WHEN DAYOFWEEK(`SentDate`) > 1 THEN ADDDATE(`SentDate`, 8-DAYOFWEEK(`SentDate`))
END)) =
DATE(DATE_SUB((CASE WHEN DAYOFWEEK(CURDATE()) = 1 THEN CURDATE()
WHEN DAYOFWEEK(CURDATE()) > 1 THEN ADDDATE(CURDATE(), 8-DAYOFWEEK(CURDATE()))
END), INTERVAL 1 WEEK))
)
THEN `NumberDelivered` END)
Can anyone advise?
Thank you,
Summer
Best Answer
-
Thank you for responding!
I've found a solution,
For last 4 weeks:
CASE WHEN YEARWEEK(`SentDate`,1) >= YEARWEEK(CURDATE(),1)-4 AND YEARWEEK(`SentDate`,1) < YEARWEEK(CURDATE(),1) THEN `METRICNAME` END
For last 8 weeks:
((CASE WHEN YEARWEEK(`SentDate`,1) >= YEARWEEK(CURDATE(),1)-8 AND YEARWEEK(`SentDate`,1) < YEARWEEK(CURDATE(),1) THEN `METRICNAME` END)
-
(CASE WHEN YEARWEEK(`SentDate`,1) >= YEARWEEK(CURDATE(),1)-4 AND YEARWEEK(`SentDate`,1) < YEARWEEK(CURDATE(),1) THEN `METRICNAME` END))
0
Answers
-
Hi Summer,
Can you just do something like ...
CASE WHEN SentDate >= current_date - interval 28 day then 'Last Week'
WHEN SentDate >= current_date - interval 56 day and SentDate < current_date - interval 28 day then 'Previous Week'
WHEN SentDate >= DATE_SUB((DATE_SUB(current_date, INTERVAL 1 year)), INTERVAL 28 DAY) and SentDate <= current_date - interval 1 year as 'Prior Year' ENDThen this would be your x axis and NumberDelivered could be your Y Axis
Hope this helps,
Brian
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.1 -
Thank you for responding!
I've found a solution,
For last 4 weeks:
CASE WHEN YEARWEEK(`SentDate`,1) >= YEARWEEK(CURDATE(),1)-4 AND YEARWEEK(`SentDate`,1) < YEARWEEK(CURDATE(),1) THEN `METRICNAME` END
For last 8 weeks:
((CASE WHEN YEARWEEK(`SentDate`,1) >= YEARWEEK(CURDATE(),1)-8 AND YEARWEEK(`SentDate`,1) < YEARWEEK(CURDATE(),1) THEN `METRICNAME` END)
-
(CASE WHEN YEARWEEK(`SentDate`,1) >= YEARWEEK(CURDATE(),1)-4 AND YEARWEEK(`SentDate`,1) < YEARWEEK(CURDATE(),1) THEN `METRICNAME` END))
0
Categories
- All Categories
- Product Ideas
- 2.1K Ideas Exchange
- Data Connections
- 1.3K Connectors
- 309 Workbench
- 17 Cloud Integrations
- Data & ETL
- 2.3K Magic ETL
- 120 SQL DataFlows
- 666 Datasets
- Visualize & Apps
- 90 App Studio
- 198 Pro-code Components
- 2.6K Charting & Analyzer
- 873 Calculations & Variables (Beast Mode)
- AI & Data science
- 23 Domo AI & AI Chat
- 4 Managing AI
- 18 Jupyter Workspaces
- Automate
- 122 Workflows
- Alerts
- Distribute
- 118 Domo Everywhere
- 284 Reporting
- Manage
- 145 Governance & Security
- 489 APIs
- 11 Add-ins & Plugins
- 13 Domo Community Gallery
- 49 Product Releases
- 13 Domo University
- Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 118 Community Announcements
- 5K Archive