Column Calculation in pivot table

michiko

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 )

GrantSmith

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

MarkSnodgrass

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

michiko

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

GrantSmith

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

michiko

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