# Comparing YOY trend lines

Options
Member

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

• Coach
Options

@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.

**Check out my Domo Tips & Tricks Videos

**Make sure to any users posts that helped you.
• Member
edited September 2023
Options

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"?

• Coach
Options

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.

**Did this solve your problem? Accept it as a solution!**
• Coach
Options

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

**Check out my Domo Tips & Tricks Videos

**Make sure to any users posts that helped you.
• Member
Options

@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.

• Coach
Options

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.

**Check out my Domo Tips & Tricks Videos

**Make sure to any users posts that helped you.