Comparing YOY trend lines

NathanDorsch · 2023-09-08T15:10:30+00:00

There was an error rendering this rich post.

I'm struggling with building a chart to show YOY trends. I was able to use a combination of the following beastmodes to create the following chart from a dataset containing a small amount of data. But when I attempt to do this with a large dataset, it doesn't work (I'm wondering if it just requires too much processing).

Can anyone advise on how to do this better? I'm thinking the extensive case statements is bogging this down.

Is there an easy way to just convert all dates into the last 12 months (so if its 5/7/21, it would be converted to 5/7/23)?

X Axis BM value: Common date (Last 6 mos):

CASE
WHEN EVENT_DATE >= SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 5 MONTH) THEN SUBDATE(EVENT_DATE, INTERVAL DAYOFMONTH(EVENT_DATE) DAY)+1
WHEN EVENT_DATE >= SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 17 MONTH)
AND EVENT_DATE < SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 11 MONTH) THEN ADDDATE(SUBDATE(EVENT_DATE, INTERVAL DAYOFMONTH(EVENT_DATE) DAY)+1, INTERVAL 1 YEAR)
WHEN EVENT_DATE >= SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 29 MONTH)
AND EVENT_DATE < SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 23 MONTH) THEN ADDDATE(SUBDATE(EVENT_DATE, INTERVAL DAYOFMONTH(EVENT_DATE) DAY)+1, INTERVAL 2 YEAR)
WHEN EVENT_DATE >= SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 41 MONTH)
AND EVENT_DATE < SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 35 MONTH) THEN ADDDATE(SUBDATE(EVENT_DATE, INTERVAL DAYOFMONTH(EVENT_DATE) DAY)+1, INTERVAL 3 YEAR)
ELSE ''
END

Series BM value: Series (for Asia)

CASE
WHEN event_date >= SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 5 MONTH) THEN 'Last 6 months'
WHEN event_date >= SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 17 MONTH)
AND event_date < SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 11 MONTH) THEN '1 year ago'
WHEN event_date >= SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 29 MONTH)
AND event_date < SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 23 MONTH) THEN '2 years ago'
WHEN event_date >= SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 41 MONTH)
AND event_date < SUBDATE(SUBDATE(CURDATE(),DATEDIFF(CURDATE(),SUBDATE(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) DAY)+1)), INTERVAL 35 MONTH) THEN '3 years ago'
ELSE ''
END

Find more posts tagged with

Accepted answers

All comments

MarkSnodgrass

@NathanDorsch this video may help you where I show how to move dates from different years into the current year so that you can do a year over year comparison.

https://youtu.be/mDwSQGAjIrs

NathanDorsch

Thanks @MarkSnodgrass I should have clarified… The main issue I'm running into is trying to eliminate the currently (incomplete) month. Is there a way to pull the last 12 complete months?

So right now, I'd want to see the first column on the left be September ending in August. Below is what I see with the basic bar line POP chart. And how to I replace the "last 12 months" with "current" and "12 months ago" with "Prior Year"?

GrantSmith

Hi @NathanDorsch

Typically when I'm doing PoP analysis I'll use a custom date dimension to add more flexibility for what I want to do. I'd recommend reading this article that I've written up in the past on how to get this configured and used.

https://community-forums.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons

MarkSnodgrass

I do have a video about incomplete months as well if you want to watch it, but the short answer is to change your date range filter to 13 months and then create a beast mode that checks to see if the data is in the current month and exclude it, otherwise include it. Drag that into your filters and filter to include. Here is an example:
CASE WHEN LAST_DAY(transactiondate) = LAST_DAY(CURRENT_DATE()) THEN 'Exclude'
ELSE 'Include'
END

NathanDorsch

@MarkSnodgrass I did somethign similar where I created a "difference month" BM - PERIOD_DIFF(date_format(EVENT_DATE,'%Y%m'),date_format(now(),'%Y%m'))

I filtered to include data < 0.

The problem is then I see this… where it'll show September (from last year) twice - both on the left and right.

MarkSnodgrass

You might try just comparing the months and not the month and year because the line is showing last year which would be less than zero in this case. Also, I find the period over period charts to be particularly difficult to work with. That could also be the issue.

NathanDorsch

@GrantSmith @MarkSnodgrass This is what I'm trying to do. The chart will show the last 12 complete months and the value for the prior year's month to that? I don't want to see the current month on the right side. For right now, I'd want to see September 2022 listed on the far left side of the chart (with September 2022 as the bar vale and September 2021 as the line value).

Is there a way to do this?