Period over Period Beast Mode

JacobSLatham · 2024-08-14T15:47:30+00:00

There was an error rendering this rich post.

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.

Find more posts tagged with

Date

Beast Mode

Variables

Accepted answers

JacobSLatham

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)

END

Here's a video for people wanting a demo.

https://youtu.be/Qt25M4qGLhg?feature=shared

All comments

ArborRose

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.

DavidChurchman

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.

GrantSmith

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?

JacobSLatham

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.

ArborRose

Something to ponder

JacobSLatham

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` = '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` = '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)

END

Here's a video for people wanting a demo.

https://youtu.be/Qt25M4qGLhg?feature=shared