Compare last 12 complete months to prior 12 months

NathanDorsch · 2023-03-15T14:54:37+00:00

There was an error rendering this rich post.

I want to show data for the last 12 complete months and compare them to the prior 12 months. Is there a way to do this?

I created a beastmode (PERIOD_DIFF(date_format(Date,'%Y%m'),date_format(now(),'%Y%m'))) to allow me to filter out the current month (march). However, the chart will still show March of last year on the comparison line… looking like this.

Find more posts tagged with

Accepted answers

MarkSnodgrass

@NathanDorsch I was able to accomplish what you want to do by developing the period over period comparison in Magic ETL. I created a video that walk you through it, which is below.

In Magic ETL, you just need a few tiles:

Formula tile to create a common month/year date using the LAST_DAY() function

Group by tile to sum up your data by that common date and any other fields that you might want to filter by (state, country, for example)

Rank & Window tile to perform the Lag function, using an offset of 12 to get 12 months ago.

This assumes your data doesn't have gaps, such as missing months or else the offset will be off and you would need to inject missing months prior to the lag.

In Analyzer, you can then create a beast mode to control if you want to show incomplete months or not, like this:

case when LAST_DAY(month_year) = LAST_DAY(CURRENT_DATE()) THEN 'Incomplete Month'

ELSE 'Complete Month'

END

In the video, I added additional lags of 3, 6, and 9 months and then used the variables feature to allow the user viewing the dashboard to choose different comparison periods and the card will dynamically change.

Hope this helps!

https://youtu.be/mP8ZjakQsD4

All comments

ArborRose

Have you tried period over period chart type? Select your date range and how you want it graphed (by week, month, qtr, etc). And then select compare to 1 year ago.

NathanDorsch

Yes, the problem I'm having is I don't want to show the current month. How do I show just the last 12 complete months? And then propertly compare them to the prior 12 months (without having an extra one comparing against today's month).

MichelleH

@NathanDorsch You can use a beast mode to filter out the current month like this:

case when MONTH(`Date`) = MONTH(CURRENT_DATE()) and YEAR(`Date`) = YEAR(CURRENT_DATE()) then ‘Exclude’ else ‘Include’ end

NathanDorsch

Please look at the screenshot of the chart I showed above. I know how to remove the current month. The problem I have is figuring out how to prevent the same month from last year showing up on the line chart (where I'm using the period over period comparison).

How do I have the March data point completely removed (for both the bar and the line charts)?

ArborRose

As Michelle mentioned with the case statement, I was thinking you would need to do something like that for both the current year and the previous year. So when any year/month falls into the scope of your chart, there would be an on/off bit preventing both from showing.

ST_Superman

@NathanDorsch couldn't you just exclude both 0 and 12 from your period_diff calculation?

ArborRose

I think excluding 0 and 12 would just remove it from the bars, not the lines. He's probably got a range set for 1 through 12 to prevent 2023-Mar from showing.

NathanDorsch

Well the problem is I do want March 2022 to show up on the very left side of the bar chart. What's happening is it is showing up there and also on the line chart on the March of this year. See illustration below.

This can't be the first time anyone has wanted to show this type of bar/pop chart for just the most recent complete months…

ArborRose

I don't believe this can be done without a change to the Domo chart code. It's the way the period over period is coded. The value you circled in green is also the value represented in the area circled in red. Filtering it from one also removes it from the other.

NathanDorsch

Right… I'm just really surprised Domo has not developed this capability.

MarkSnodgrass

I think this is why the Period over Period charts aren't used too heavily as they have some pretty rigid code tied to them. I often use the standard Line + Bar chart and create a few beast modes to have a more flexible period over period chart. I walk through how to do it here:

https://youtu.be/mDwSQGAjIrs

NathanDorsch

Thanks @MarkSnodgrass I watched your video, but not seeing a way to resolve my issue. Is there a way to do what I'm attempting? If it is multi-line chart, I can live with that.

GrantSmith

You can then set your chart to be the last 13 months and create a beast mode to return either yes or no if they're less than the current month.

CASE WHEN LAST_DAY(`Date`) < LAST_DAY(CURDATE()) THEN ‘KEEP’ ELSE ‘EXCLUDE’ END

Then just filter on that beast mode for the KEEP values.

LAST_DAY is a hack I use to get the last day of a month for a given date. It's easier than comparion MONTH and YEAR and worrying about new years and the rollover of the month number.

NathanDorsch

@GrantSmith Right, I'm already doing something similar to that with a BM. The problem is with March of last year. I want it to show up in the bar chart on the very left side, but not in the line chart over the current month.

MarkSnodgrass

@NathanDorsch I was able to accomplish what you want to do by developing the period over period comparison in Magic ETL. I created a video that walk you through it, which is below.

In Magic ETL, you just need a few tiles:

Formula tile to create a common month/year date using the LAST_DAY() function

Group by tile to sum up your data by that common date and any other fields that you might want to filter by (state, country, for example)

Rank & Window tile to perform the Lag function, using an offset of 12 to get 12 months ago.

This assumes your data doesn't have gaps, such as missing months or else the offset will be off and you would need to inject missing months prior to the lag.

In Analyzer, you can then create a beast mode to control if you want to show incomplete months or not, like this:

case when LAST_DAY(month_year) = LAST_DAY(CURRENT_DATE()) THEN 'Incomplete Month'

ELSE 'Complete Month'

END

Hope this helps!

https://youtu.be/mP8ZjakQsD4

NathanDorsch

Thanks @MarkSnodgrass that is the endstate of what I want and may resort to this approach. Wish I could do it without having to aggregate data in the ETL though.