How to show a month position as at 12 months ago

Kyle_Russell
Kyle_Russell Member
in Cards, Dashboards, Stories

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!

Tagged:

Best Answer

  • Sean_Tully
    Sean_Tully Contributor
    edited April 30 Answer ✓

    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.

Answers

  • Sean_Tully
    Sean_Tully Contributor
    edited April 30 Answer ✓

    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
    Kyle_Russell Member

    Hi Sean,

    This worked perfectly, thanks!

  • GrantSmith
    GrantSmith Coach

    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:

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

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

Categories