Period over Period Beast Mode
I have the following Beast Mode working for a comparison table (Period over Period is the name of the variable:
CASE
WHEN Period Over Period
= 'Week over Week' THEN (CASE WHEN WEEK(Date
) = WEEK(CURDATE() - INTERVAL 1 WEEK) AND YEAR(Date
) = YEAR(CURDATE() - INTERVAL 1 WEEK) AND DAYOFWEEK(Date
) <= DAYOFWEEK(CURDATE()) THEN Amount
END)
WHEN Period Over Period
= 'Month over Month' THEN (CASE WHEN MONTH(Date
) = MONTH(CURDATE() - INTERVAL 1 MONTH) AND YEAR(Date
) = YEAR(CURDATE() - INTERVAL 1 MONTH) AND DAYOFMONTH(Date
) <= DAYOFMONTH(CURDATE()) THEN Amount
END)
WHEN Period Over Period
= 'Quarter over Quarter' THEN (CASE WHEN QUARTER(Date
) = QUARTER(CURDATE() - INTERVAL 1 QUARTER) AND YEAR(Date
) = YEAR(CURDATE() - INTERVAL 1 QUARTER) AND DAYOFYEAR(Date
) <= DAYOFYEAR(CURDATE() - 91) THEN Amount
END)
WHEN Period Over Period
= 'Year over Year' THEN (CASE WHEN YEAR(Date
) = YEAR(CURDATE() - INTERVAL 1 YEAR) AND Date
< DATE_SUB(CURDATE(), INTERVAL 1 YEAR) THEN Amount
END)
END
What I want to add is 'Week over same Week Last Year', 'Month over same Month Last Year', and 'Quarter over same Quarter Last Year'. And if possible, 'Last 12 Months' to compare to the previous 12 month period (this is probably the hardest to execute in a beast mode).
Is there a way of adding the above to the beast mode? I know I could do in the ETL, just wanting to see if this is possible in beast mode.
Best Answer
-
Update: @GrantSmith and @ArborRose, I figured most of it out (Week over same Week LY, Month over same Month LY, and Quarter over same Quarter LY).
The following code is for beast modes Period 1 (labeled as Current Period) and Period 2 (labeled as Previous Period)
Period 1:
CASE
WHEN `Period Over Period` = 'Week over Week' THEN (CASE WHEN WEEK(`Date`) = WEEK(CURDATE()) AND YEAR(`Date`) = YEAR(CURDATE()) THEN `Amount` END)WHEN `Period Over Period` = 'Month over Month' THEN (CASE WHEN MONTH(`Date`) = MONTH(CURDATE()) AND YEAR(`Date`) = YEAR(CURDATE()) THEN `Amount` END)
WHEN `Period Over Period` = 'Quarter over Quarter' THEN (CASE WHEN QUARTER(`Date`) = QUARTER(CURDATE()) AND YEAR(`Date`) = YEAR(CURDATE()) THEN `Amount` END)
WHEN `Period Over Period` = 'Year over Year' THEN (CASE WHEN YEAR(`Date`) = YEAR(CURDATE()) THEN `Amount` END)
WHEN `Period Over Period` = 'Week over same Week LY' THEN (CASE WHEN WEEK(`Date`) = WEEK(CURDATE()) AND YEAR(`Date`) = YEAR(CURDATE()) THEN `Amount` END)WHEN `Period Over Period` = 'Month over same Month LY' THEN (CASE WHEN MONTH(`Date`) = MONTH(CURDATE()) AND YEAR(`Date`) = YEAR(CURDATE()) THEN `Amount` END)
WHEN `Period Over Period` = 'Quarter over same Quarter LY' THEN (CASE WHEN QUARTER(`Date`) = QUARTER(CURDATE()) AND YEAR(`Date`) = YEAR(CURDATE()) THEN `Amount` END) END
Period 2:
CASE
WHEN `Period Over Period` = 'Week over Week' THEN (CASE WHEN WEEK(`Date`) = WEEK(CURDATE() - INTERVAL 1 WEEK) AND YEAR(`Date`) = YEAR(CURDATE() - INTERVAL 1 WEEK) AND DAYOFWEEK(`Date`) <= DAYOFWEEK(CURDATE()) THEN `Amount` END)
WHEN `Period Over Period` = 'Month over Month' THEN (CASE WHEN MONTH(`Date`) = MONTH(CURDATE() - INTERVAL 1 MONTH) AND YEAR(`Date`) = YEAR(CURDATE() - INTERVAL 1 MONTH) AND DAYOFMONTH(`Date`) <= DAYOFMONTH(CURDATE()) THEN `Amount` END)
WHEN `Period Over Period` = 'Quarter over Quarter' THEN (CASE WHEN QUARTER(`Date`) = QUARTER(CURDATE() - INTERVAL 1 QUARTER) AND YEAR(`Date`) = YEAR(CURDATE() - INTERVAL 1 QUARTER) AND DAYOFYEAR(`Date`) <= DAYOFYEAR(CURDATE() - 91) THEN `Amount` END)WHEN `Period Over Period` = 'Year over Year' THEN (CASE WHEN YEAR(`Date`) = YEAR(CURDATE() - INTERVAL 1 YEAR) AND `Date` < DATE_SUB(CURDATE(), INTERVAL 1 YEAR) THEN `Amount` END)
WHEN `Period Over Period` = 'Week over same Week LY' THEN (CASE WHEN WEEK(`Date`) = WEEK(CURDATE()) AND YEAR(Date) = YEAR(CURDATE()) - 1 THEN `Amount` END)
WHEN `Period Over Period` = 'Quarter over same Quarter LY' THEN (CASE WHEN QUARTER(`Date`) = QUARTER(CURDATE()) AND YEAR(Date) = YEAR(CURDATE()) - 1 THEN `Amount` END)
WHEN `Period Over Period` = 'Month over same Month LY' THEN (CASE WHEN MONTH(`Date`) = MONTH(CURDATE()) AND YEAR(Date) = YEAR(CURDATE()) - 1 THEN `Amount` END)
ENDHere's a video for people wanting a demo.
0
Answers
-
Weeks don't match year over year. You may have a year with 3 business days in a week, comparing with another year with a different number of business days.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
You could do this with no Beastmodes by playing with the date range, "graph by" and "compare to" in any of "Period over Period" charts.
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0 -
Month or Quarter over last year's quarter would be something like:
WHEN `Period Over Period` = 'Quarter over Quarter LY' THEN (CASE WHEN QUARTER(Date) = QUARTER(CURDATE()) AND YEAR(Date) = YEAR(CURDATE())-1 THEN Amount END)
Repeat the same for MONTH
WHEN `Period Over Period` = 'Month over Month LY' THEN (CASE WHEN MONTH(Date) = MONTH(CURDATE()) AND YEAR(Date) = YEAR(CURDATE())-1 THEN Amount END)
As @ArborRose mentioned Weeks are a bit tricky because they can vary, it depends on what you consider last year's week. Is the the week number? Is it based on the start of the week? Should partial weeks be counted? Are you considering last year to be 52 weeks ago or 365 days ago?
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thanks @GrantSmith, your code works to provide me with the previous period Amount. I'm working on tweaking it so that the current period appears with it as well (as it does for my code above).
As for 'Week over same Week Last Year', I'm looking for the Week Number of the year to match the Week Number of the current year. I'm not concerned with day differences as my company conducts business every day of the week. So if we are in Week 33 currently, I want to see Week 33 for the previous year.
0 -
Something to ponder
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
Update: @GrantSmith and @ArborRose, I figured most of it out (Week over same Week LY, Month over same Month LY, and Quarter over same Quarter LY).
The following code is for beast modes Period 1 (labeled as Current Period) and Period 2 (labeled as Previous Period)
Period 1:
CASE
WHEN `Period Over Period` = 'Week over Week' THEN (CASE WHEN WEEK(`Date`) = WEEK(CURDATE()) AND YEAR(`Date`) = YEAR(CURDATE()) THEN `Amount` END)WHEN `Period Over Period` = 'Month over Month' THEN (CASE WHEN MONTH(`Date`) = MONTH(CURDATE()) AND YEAR(`Date`) = YEAR(CURDATE()) THEN `Amount` END)
WHEN `Period Over Period` = 'Quarter over Quarter' THEN (CASE WHEN QUARTER(`Date`) = QUARTER(CURDATE()) AND YEAR(`Date`) = YEAR(CURDATE()) THEN `Amount` END)
WHEN `Period Over Period` = 'Year over Year' THEN (CASE WHEN YEAR(`Date`) = YEAR(CURDATE()) THEN `Amount` END)
WHEN `Period Over Period` = 'Week over same Week LY' THEN (CASE WHEN WEEK(`Date`) = WEEK(CURDATE()) AND YEAR(`Date`) = YEAR(CURDATE()) THEN `Amount` END)WHEN `Period Over Period` = 'Month over same Month LY' THEN (CASE WHEN MONTH(`Date`) = MONTH(CURDATE()) AND YEAR(`Date`) = YEAR(CURDATE()) THEN `Amount` END)
WHEN `Period Over Period` = 'Quarter over same Quarter LY' THEN (CASE WHEN QUARTER(`Date`) = QUARTER(CURDATE()) AND YEAR(`Date`) = YEAR(CURDATE()) THEN `Amount` END) END
Period 2:
CASE
WHEN `Period Over Period` = 'Week over Week' THEN (CASE WHEN WEEK(`Date`) = WEEK(CURDATE() - INTERVAL 1 WEEK) AND YEAR(`Date`) = YEAR(CURDATE() - INTERVAL 1 WEEK) AND DAYOFWEEK(`Date`) <= DAYOFWEEK(CURDATE()) THEN `Amount` END)
WHEN `Period Over Period` = 'Month over Month' THEN (CASE WHEN MONTH(`Date`) = MONTH(CURDATE() - INTERVAL 1 MONTH) AND YEAR(`Date`) = YEAR(CURDATE() - INTERVAL 1 MONTH) AND DAYOFMONTH(`Date`) <= DAYOFMONTH(CURDATE()) THEN `Amount` END)
WHEN `Period Over Period` = 'Quarter over Quarter' THEN (CASE WHEN QUARTER(`Date`) = QUARTER(CURDATE() - INTERVAL 1 QUARTER) AND YEAR(`Date`) = YEAR(CURDATE() - INTERVAL 1 QUARTER) AND DAYOFYEAR(`Date`) <= DAYOFYEAR(CURDATE() - 91) THEN `Amount` END)WHEN `Period Over Period` = 'Year over Year' THEN (CASE WHEN YEAR(`Date`) = YEAR(CURDATE() - INTERVAL 1 YEAR) AND `Date` < DATE_SUB(CURDATE(), INTERVAL 1 YEAR) THEN `Amount` END)
WHEN `Period Over Period` = 'Week over same Week LY' THEN (CASE WHEN WEEK(`Date`) = WEEK(CURDATE()) AND YEAR(Date) = YEAR(CURDATE()) - 1 THEN `Amount` END)
WHEN `Period Over Period` = 'Quarter over same Quarter LY' THEN (CASE WHEN QUARTER(`Date`) = QUARTER(CURDATE()) AND YEAR(Date) = YEAR(CURDATE()) - 1 THEN `Amount` END)
WHEN `Period Over Period` = 'Month over same Month LY' THEN (CASE WHEN MONTH(`Date`) = MONTH(CURDATE()) AND YEAR(Date) = YEAR(CURDATE()) - 1 THEN `Amount` END)
ENDHere's a video for people wanting a demo.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 744 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive