Column Calculation in pivot table

michiko
michiko Contributor
in Charting

I just build a pivot table with

Year as column & dynamic filters ( so that column is dynamic )

Month as row

Amount as values


When I select year 2020 and 2018, the pivot table displays the values as expected.

The question is, how do I add column calculation to show the variance between the two years selected ( assuming we made that only 2 selections can be made )

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You may want to think about restructuring your data and using a date dimension to calculate the current day, last year, two years ago etc for your data points. Then you can use a beast mode to pull only the offsets to you want. This will then allow you to be able to calculate the current year from a year or two years ago. The downside is that you can't easily have your columns say the year but it'd be something like "Last Year" or "Two Years Ago". I've done a write up previously on this methodology here: A more flexible way to do Period over Period comparisons

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

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    Pretty sure you can't do what you want to do with a pivot table. However, you can customize a flex table to those values calculated. Here's a link to the KB article to show you how to customize it.

    https://domohelp.domo.com/hc/en-us/articles/360043429073-Flex-Table

    **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.
  • michiko
    michiko Contributor

    Cheers for replying - Flex won't solve the problem as the end result is to complicate the table structure even further by nesting year under another dimension. ie

    image.png


  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You may want to think about restructuring your data and using a date dimension to calculate the current day, last year, two years ago etc for your data points. Then you can use a beast mode to pull only the offsets to you want. This will then allow you to be able to calculate the current year from a year or two years ago. The downside is that you can't easily have your columns say the year but it'd be something like "Last Year" or "Two Years Ago". I've done a write up previously on this methodology here: A more flexible way to do Period over Period comparisons

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

    @GrantSmith, I saw and read many of your blogs - Many thanks for sharing the tips & tricks with the community !! 👍️

    Yes correct, a different data structure will definitely work although I was reaaaaally hoping for a cool pivot table function calculation feature 😛. Instead, I ended up with a cross-join structure as below.

    image.png


Categories