1 2 Previous Next 17 Replies Latest reply on Feb 3, 2016 6:26 AM by paul kessels

# Calculate delta between two dimensions with common values

Hi, I have been struggling with something that sound like a simple calculation to me, but haven't figured out how to do it yet.

I have a dataset with customers. Each customer is a in customer segment. Between years, they might move between segments (change segment). I want to calculate the delta between the number of unique customers in each segment.

Current Segment (countd of all customers in segments A to E):

 A 3 B 5 C 7 D 14 E 153

New Segment:

 A 4 B 8 C 10 D 10 E 150

What I now want to create is a table/graph with the following result:

 Segment Delta A 1 B 3 C 3 D -4 E -3

I have added this sample set with 3 sheets to this post. First 2 sheets is easy, but who can help me in the right direction with the Delta calc?

• ###### 1. Re: Calculate delta between two dimensions with common values

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

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

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 self-explanatory!

• ###### 4. Re: Calculate delta between two dimensions with common values

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

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

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

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

Yes, 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:

1 of 1 people found this helpful
• ###### 9. Re: Calculate delta between two dimensions with common values

Brilliant, works like a charm! You made my day

Thanks!!!

• ###### 11. Re: Calculate delta between two dimensions with common values

Hi Pooja (or someone else reading this).

Everything works perfectly. But now I got across a data-set 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

I added the revised sample set

• ###### 13. Re: Calculate delta between two dimensions with common values

Paul,

Change the formula of the 'new calc' to accommodate 0s for all null values.

1 of 1 people found this helpful
• ###### 14. Re: Calculate delta between two dimensions with common values

pfff...why can't I figure this out myself

A million thanks again!

1 2 Previous Next