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
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 472 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 202 Visualize
- 255 Beast Mode
- 2.1K Charting
- 12 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 180 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive