7 Replies Latest reply on Aug 26, 2016 5:58 AM by Mark Fraser

# How to calculate difference between presented columns which are part of one dimension?

Hey

I have a simple question that I can't seem to find an answer to anywhere. I need to calculate a difference between two columns that are part of one dimension.

How can I do that? My end state is to have another column after 2016 AOP showing me difference of "7+5" vs AOP, another after 2015 act and so on.

Any help will be very much appreciated.

• ###### 1. Re: How to calculate difference between presented columns which are part of one dimension?

You would create the fields separately - i.e.

7+5: IF [version] = '7+5' THEN [Values] END

2016AOP: IF [version] = '2016 AOP' THEN [Values] END

The difference can then be calculated using those new fields  - for example you could have SUM([7+5]) / SUM([2016AOP]) - 1

• ###### 2. Re: How to calculate difference between presented columns which are part of one dimension?

make a calculated field like?

str(int(LEFT([7+5], FIND([7+5], ","))) - int(LEFT([2016 AOP], FIND([2016 AOP], ",")))) + "," + str(int(MID([7+5], FIND([7+5], ",")+1)) - int(MID([2016 AOP], FIND([2016 AOP], ",")+1)))

• ###### 3. Re: How to calculate difference between presented columns which are part of one dimension?

Corey, thank you for looking into this. I tried creating a calculated field like that however I did not manage to make it work at all. Something is wrong in that formula plus I don’t want to simply get a solution I would like to understand how to solve it on my own.

• ###### 4. Re: How to calculate difference between presented columns which are part of one dimension?

Andrew, thanks for looking into that. I tried what you wrote and it works only partially. It does give me an option to create columns with values depending on  however I encounter two problems:

1. Tableau still needs to have something as a “mark” to show values. With your approach I get LOBs and next column is values for 7+5 and another is blank with “abc” showing. I end up with the same problem when I change my source data to have different versions as columns.

2. I added different versions as different calculations and was able to come up with a difference between them however when I tried putting them the way I want (75, AOP, vs AOP, 2015, vs 2015, 66, vs 6+6) the table started to merge same columns and did not show properly. On top of that problem number 1 was still there – Tableau expected me to put something as a “mark”.

My issue is still not resolved, unfortunately. It should be very simple but drives me crazy the amount of time I spent on this.