YTD Previus Year

SGPA

Hello,

I want to make a dashboard where i will have 2 single value type cards, one with the YTD for the current year and the other with YTD for the previus year. I've computed both values trough ETL with the rank/window tile. The column has both the values for the previous and current YTD (see image below). However, this method has issues for a dynamic dashboard with filters.

What i would like to have is something like this (see image below).

With ETL, how can i achieve something like this? Thank you

RobSomers

@SGPA You could also create a another date column using a formula that is the previous year (ex: Jan/22 would have Jan/21 in the new column) and then join the table on itself using the original date column and the previous year column and just rename the AVG YTD column from the previous year join to AVG YTD Previous Year.

Jones01

@SGPA what does the raw data look like as you may be able to do this in a beastmode?

SGPA

@Jones01 the raw data is what you see in the first picture

GrantSmith

I’d recommend reformatting your dataset to use a custom date offset dimension dataset. This will allow the interactions with the page date filters and simplify your data structure. I’ve done a write up on this previously here: https://dojo.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest

MarkSnodgrass

Assuming you have data in every month, you can accomplish this with a group by tile followed by a rank and window tile. Use a group by tile to group by your month/year (as a date field) and choose Average for your aggregation type. Add the Rank and Window tile and choose the Lag function and create a column called Previous Year and select your average column that you created in the group by tile and enter an offset of 12 and sort by your date field ascending.

RobSomers

@SGPA You could also create a another date column using a formula that is the previous year (ex: Jan/22 would have Jan/21 in the new column) and then join the table on itself using the original date column and the previous year column and just rename the AVG YTD column from the previous year join to AVG YTD Previous Year.