How to show a month position as at 12 months ago

Kyle_Russell

Hi all,

I'm working on a set of data and for one of my cards I would like to show a pie chart of the total position for the current month and a separate pie chart for the position as at 12 months ago. I'm not sure how best to show this as the date options only seem to allow for the cumulative last 12 months - I could obviously use filters but this would take away from the autonomy of the report.

Any suggestions would be greatly appreciated!

Sean_Tully

I would probably create a Beast Mode to find the '12 months ago' data, and use that as a filter on my card so that I'm only showing that data.

I don't know the fields in your data, but it would probably look like:

case when date_sub(date_format(current_date(), '%Y-%m-01'), interval 12 month) = date_format(your_date_field, '%Y-%m-01') then 'Y' else 'N' end

When you drag that to your filter and choose 'Y', the pie chart should only show data from 12 months ago, and will move to the new '12 months ago' every month.

Sean_Tully

I would probably create a Beast Mode to find the '12 months ago' data, and use that as a filter on my card so that I'm only showing that data.

I don't know the fields in your data, but it would probably look like:

case when date_sub(date_format(current_date(), '%Y-%m-01'), interval 12 month) = date_format(your_date_field, '%Y-%m-01') then 'Y' else 'N' end

When you drag that to your filter and choose 'Y', the pie chart should only show data from 12 months ago, and will move to the new '12 months ago' every month.

Kyle_Russell

Hi Sean,

This worked perfectly, thanks!

GrantSmith

You can have a bit more flexibility if you restructure your data using a date dimension table with custom offsets so you can then filter on any time frame and get the 12 months ago for the exact same timeframe instead of just the current timeframe. I've done some write-ups in the past about how to get this configured and setup with code examples. Feel free to take a look:

A more flexible way to do Period over Period comparisons

GrantSmith

Sep 21, 2021

Greetings! I see variations of this topic asked several times so I thought I'd write up all of the information in a singular spot for easier reference. Below I'll outline how I utilize my own date dimension table to have customized period over period charts, compare the current partial month to the same amount of time in the previous period (no more seeing your period over period percentage tank because the month isn't complete yet). I will also go over how to configure and set this up yourselves. Let's get started.

---

View Post