Column Calculation in pivot table

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
-
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!**1
Answers
-
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
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
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
0 -
@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.
0
Categories
- 11K All Categories
- 5 Private Company Board
- 2 APAC User Group
- 12 Welcome
- 42 Domo News
- 9.9K Using Domo
- 2K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 349 Workbench
- 267 Domo Best Practices
- 11 Domo Certification
- 474 Domo Developer
- 51 Domo Everywhere
- 110 Apps
- 725 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 29 お知らせ
- 65 Kowaza
- 301 仲間に相談
- 662 ひらめき共有