Last Month and Same Month Last Year

Hiraayub · 2021-12-10T17:08:10+00:00

There was an error rendering this rich post.

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.

Find more posts tagged with

Accepted answers

All comments

GrantSmith

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.

MarkSnodgrass

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:

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.

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.

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

Hope this helps.

Hiraayub

@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

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.