YTD Previus Year

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


Best Answer

  • RobSomers
    RobSomers Coach
    Answer ✓

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

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

Answers

  • Jones01
    Jones01 Contributor

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

  • SGPA
    SGPA Member

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

  • 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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • 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.

    **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.
  • RobSomers
    RobSomers Coach
    Answer ✓

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

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**