I am trying to create a treemap that shows the change in revenue from a Base year to a Comparison year (e.g. change in revenue from 2014 to 2017). The Base and Comparison years are parameters that the user can control. I would like to create a calculated field that is able to subtract the Base year revenue from the Comparison year revenue without having to have the Year dimension on any shelf (as this doesn't really work with treemaps). So far I've created the calculated field that correctly identifies the revenue in the Base and Comparison years (fairly straightforward) but now I'm having trouble being able to subtract, for example, 2014 revenue from 2017 revenue as Tableau seems to not want to look across years.
See below for a table showing what I'm working with.
Comparing 2014 and 2017
|Base Year (2014)||1000|
|Comparison year (2014)||1300|
|Change in Revenue|
The change in revenue line Null because there are no values for Base Year in 2017.
Because the years are different I can't figure out a way to subtract 1000 from 1300 without Lookup or table calculations but these won't work in a treemap. All I need is for one of these years to have the appropriate change values in it because then the treemap will ignore the empty values and just pick up the change between the Base and Comparison.
Is there a way to do this while also retaining the ability to slice the data across various dimensions (e.g. region, product, market segment, etc.)?
I've googled around but haven't found anything that addresses the problem I'm having with the different years. I should also note that these aren't actually dates, I just have a year value.
Unfortunately the data I'm using is confidential so I can't share the actual workbook but I've recreated what I've got so far in the attached Superstore workbook.
Message was edited by: Peter Hix
Superstore Example.twbx 351.5 KB