Date Range - Last 3 mo compared to same months last year.

Morgan
Morgan Member
in Charting

Hi there,

I am trying to use a multi-value card in Domo and I wanted to compare the last 3 months to the same 3 months of the prior year.

i.e. Dec 23, Jan 24, Feb 24 compared to Dec 22, Jan 23, Feb 23.

I wasn't sure how exactly the compare to months works. Whether or not that compare to __ months would be alluding to x months ago or how it's justified.

image.png

It's not very easy to trouble shoot unfortunately so I was hoping that someone might have the answer already, so I don't have to dig in for too long :) Thanks!

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    You can do this with the multi-value gauge card, which is one of my favorite cards. You are going to want to do a few things to make this work.

    First, set your date range filter to the last 16 months. This will allow the card to use data back to December 2022 as it counts the current month as a month.

    Second, create a beast mode that puts your date field into the 3 month groups like this:

    CEILING(
    
period_diff(date_format(CURRENT_DATE(), '%Y%m'), date_format(date, '%Y%m'))
    
/3
    
)

    It can be helpful to start in a table card and see how this beast mode will group your data.

    image.png

    As you can see it is grouping your dates into different numbered groups. We are going to use the beast mode in the group by and the sorting in your gauge card.

    image.png

    In your chart properties - Change Value Options - Data select First and Last Values

    This will tell the card to use the first 3 month group and the last 3 month group for comparison.

    The rest of the chart properties will depend on what exactly you want to display.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    You can do this with the multi-value gauge card, which is one of my favorite cards. You are going to want to do a few things to make this work.

    First, set your date range filter to the last 16 months. This will allow the card to use data back to December 2022 as it counts the current month as a month.

    Second, create a beast mode that puts your date field into the 3 month groups like this:

    CEILING(
    
period_diff(date_format(CURRENT_DATE(), '%Y%m'), date_format(date, '%Y%m'))
    
/3
    
)

    It can be helpful to start in a table card and see how this beast mode will group your data.

    image.png

    As you can see it is grouping your dates into different numbered groups. We are going to use the beast mode in the group by and the sorting in your gauge card.

    image.png

    In your chart properties - Change Value Options - Data select First and Last Values

    This will tell the card to use the first 3 month group and the last 3 month group for comparison.

    The rest of the chart properties will depend on what exactly you want to display.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • MarkSnodgrass
    MarkSnodgrass Coach

    Also, if you have data from the current month coming in and you don't want it to be considered, you can add a beast mode that looks like this and add it to the filters to remove it.

    CASE WHEN LAST_DAY(CURRENT_DATE()) = LAST_DAY(date) THEN 'Exclude' ELSE 'Include' END

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Morgan
    Morgan Member

    @MarkSnodgrass That was extremely helpful! Thank you so much!

Categories