Last Month and Same Month Last Year

Hiraayub
Hiraayub Member
in Charting

I'm working on ETL do get the last month data and same month last year.

As it will be rolling so I don't want to do the calculation calling the Month November only in the data. (like if last month is Oct then the result will be Oct 2021 & Oct 20202 and if the last month is Nov then Nov 2021 & Nov 2020 and so on)

Any Ideas, how I can achieve this.

Answers

  • GrantSmith
    GrantSmith Coach

    Hi @Hiraayub When I'm dealing with date comparisons like this I use a custom date dimension to easily define offsets like "last year". I've done a writeup on this in the past: https://dojo.domo.com/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest to get the value for the current date or the date from last year / last month etc.

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

    You can do this in the ETL with a combination of the formula tile to get the first day of the month, a group by tile and a rank and window that uses the lag function. Here are screenshots of what I just did to compare counts to the same month of the previous year:

    image.png

    The formula tile just has a field to get the first day of the month so that you can align your data for the subsequent group by tile

    DATE_SUB(`datereported`, INTERVAL (DAYOFMONTH(`datereported`) - 1) DAY)

    The group by tile groups the first day of the month field that was created in the formula tile and counts whatever unique field you want to count.

    image.png

    The Rank & Window tile uses the Lag function and lag by 12 to get the count for the same month in the previous year. This assumes you have data in every month. If not, you will need the data dimension dataset to make sure there are no gaps.

    image.png

    This will create a new column called PreviousYear that has the total from the same month in the previous year.

    Hope this helps.

    **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.
  • Hiraayub
    Hiraayub Member

    @MarkSnodgrass there are gaps in month for few customer, the data dimension dataset you are refereeing to is the same things Grant is explaining?

  • MarkSnodgrass
    MarkSnodgrass Coach

    Yes. If you don't already have the date dimension dataset added to your data center, you can add it by going to the Data Center, click on Connectors, search for Dimension and select the Domo Dimensions connector and then choose calendar.csv from the list of files. You would then add this dataset to your ETL. You will need to add a filter tile to just get the first day of the month (filter to day = 1) and then do a left join to you main dataset and use this date field in the rest of the tiles that I described. This will eliminate the gaps for you.

    image.png
    image.png


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

Categories