Period over Period Beast Mode

TheScotsman
TheScotsman Member
in 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.

Tagged:

Best Answer

  • TheScotsman
    TheScotsman Member
    edited August 20 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)


    END

    Here's a video for people wanting a demo.

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

Answers

  • ArborRose
    ArborRose Coach

    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! **

  • DavidChurchman
    DavidChurchman Coach

    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.

  • GrantSmith
    GrantSmith Coach

    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!**
  • TheScotsman
    TheScotsman Member
    edited August 16

    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.

    Comparison Table (original).png
    Comparison Table (additions).png

  • ArborRose
    ArborRose Coach

    Something to ponder

    image.png

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • TheScotsman
    TheScotsman Member
    edited August 20 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)


    END

    Here's a video for people wanting a demo.

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

Categories