
1. Re: Calculate delta between two dimensions with common values
pooja.gandhi Jan 7, 2016 7:39 AM (in response to paul kessels)Paul,
First pivot the data in the data connection window (pivot only works for a few data sources for Tableau version 9.0 or higher). Assuming you are using a data source that supports the pivot. Then its all pretty easy:
Calculation 1: IF [Pivot field names] = 'Segment new' then [Customer] end
Calculation 2: IF [Pivot field names] = 'Segment old' then [Customer] end
Calculation 3: COUNTD([Calculation1])  COUNTD([Calculation2])

2. Re: Calculate delta between two dimensions with common values
paul kessels Jan 7, 2016 8:26 AM (in response to pooja.gandhi)Thanks Pooja!
So, the good news is that it is solvable :) The bad news is that I can't figure out how to create the pivot. I have searched and found several instructions, but my screen looks different (I don't have the pivot option / drop down on the column name). Any idea?
Could you attach the .twbx please so I can have a look?
How would a dataset need to be setup for pivoting? Could it still be done without having the availability of the pivoting option?

3. Re: Calculate delta between two dimensions with common values
pooja.gandhi Jan 7, 2016 8:32 AM (in response to paul kessels)Paul,
You will pivot in the data connection window, not in the Tableau view. Which version of Tableau are you using? I have version 9.2 only, so if you are on an earlier version, you will not be able to open the workbook. But pivot is created here:
Select both the columns, and click pivot (only available in version 9.0 or higher)
After you do this, all should be pretty selfexplanatory!

4. Re: Calculate delta between two dimensions with common values
paul kessels Jan 7, 2016 9:20 AM (in response to pooja.gandhi)ok, got it. I got it working with the above file. I'm on 9.0.
Unfortunately the original datasource doesn't allow me to do that. Any idea why? It is a SQLtable extract. When I select two column, I just have 'hide' available.
Is there any solution or alternative way of doing this?
Thanks!

5. Re: Calculate delta between two dimensions with common values
paul kessels Jan 7, 2016 9:23 AM (in response to paul kessels)Also, the original two pivoted fields do need to remain in the dataset without change, so Pivoting doesn't sound like the solution here.
Can this be done with table calculations maybe?

6. Re: Calculate delta between two dimensions with common values
pooja.gandhi Jan 7, 2016 9:43 AM (in response to paul kessels)Paul,
Aah, bummer. You may be able to Pivot the data at the query level using custom SQL. I haven't played with the pivot syntax in SQL but that could be one way to do it.
Because I don't think you will be able to use Tableau's inbuilt pivot functionality for a SQL datasource. And I am not aware of another way to get the results.
Pooja.

7. Re: Calculate delta between two dimensions with common values
paul kessels Jan 7, 2016 10:18 AM (in response to pooja.gandhi)Hmm...bummer indeed. I cannot imagine there is not a way though, it seems like such a simple request :)
Thinking out loud, If not table calc, could LOD calculations offer a solution maybe? Or joined/combined fields with some logic, since the segments are finite.

8. Re: Calculate delta between two dimensions with common values
pooja.gandhi Jan 7, 2016 10:32 AM (in response to paul kessels)1 of 1 people found this helpfulYes, you could possibly do this with an LOD approach. I did something like this:
Dragged 'segment old' on rows then place 'segment new' next to it on rows.
Then I placed a calculation for new segment like:
New: { fixed [Segment new] : countd([Customer]) }
calculation for old segment like:
Old: { fixed [Segment old] : countd([Customer]) }
Because the 1st pill is 'segment old' the grouping will be based on that pill.
Then I said, IF segment old = segment new, then give me the value of the new calc, so something like this:
New Calc: IF [Segment old] = [Segment new] then [New] end after bringing this on the view, I used an average aggregation.
And finally subtraction calc is : I used an average aggregation for this calc also. See the measure values section in the image below.
[New Calc]  [Old]
Basically drag segment old, segment new to rows and double click on all the 4 calcs you created. Then take off segment new from the rows and sum(new) from measure values, and you are left with this:

9. Re: Calculate delta between two dimensions with common values
paul kessels Jan 8, 2016 12:41 AM (in response to pooja.gandhi)Brilliant, works like a charm! You made my day
Thanks!!!

10. Re: Calculate delta between two dimensions with common values
pooja.gandhi Jan 8, 2016 5:20 AM (in response to paul kessels)I am glad that worked!

11. Re: Calculate delta between two dimensions with common values
paul kessels Feb 2, 2016 9:32 AM (in response to pooja.gandhi)Hi Pooja (or someone else reading this).
Everything works perfectly. But now I got across a dataset where some of the values/entries do not exist.
For example, A is existing in the 'old' values, but not in the 'new' values.
I am trying to get it still to who 0 in the first column and 3 in the last, but cannot get it done.
Segment old Avg. New Calc Old Avg. Subtraction A 3.0 B 11.0 5.0 6.0 C 13.0 7.0 6.0 D 11.0 14.0 3.0 E 151.0 153.0 2.0 Any ideas?

12. Re: Calculate delta between two dimensions with common values
paul kessels Feb 2, 2016 9:34 AM (in response to paul kessels)I added the revised sample set

13. Re: Calculate delta between two dimensions with common values
pooja.gandhi Feb 2, 2016 9:39 AM (in response to paul kessels)1 of 1 people found this helpful 
14. Re: Calculate delta between two dimensions with common values
paul kessels Feb 2, 2016 10:06 AM (in response to pooja.gandhi)pfff...why can't I figure this out myself
A million thanks again!