5 Replies Latest reply on Jul 14, 2016 12:26 AM by Łukasz Majewski

# Number of common UIDs between years

Hello everyone,

I have some records and two of the dimensions are: registrationYear and UID. For each year, a number of different UIDs appear.

The same UID can appear in combination with a different year. The UIDs are strings.

I place registrationYear in column and row, and the COUNT(UID) in the Label.

This setup gives me a value only in the diagonal of the table! What I want to do is have a table as the following:

2003 2004 2005 2006 ...

2003

2004                        X

2005

2006

with all the cells filled with the number of UIDs that are common between the 2 years. For example, in the first cell (2003, 2003) I want the number of UIDs that appear in 2003.

In the second row (2004), third column (2005), marked by an X, I want the number of UIDs that appear in 2004, but also appear in 2005.

Is this possible in Tableau?

Bill.

• ###### 1. Re: Number of common UIDs between years

This should be possible.

Could you share a sample set of data to tinker with.

Kind Regards,

Khalid

• ###### 2. Re: Number of common UIDs between years

I cannot share any of the data, as they are sensitive

However, I can provide a very small dataset that represents the original. Is this ok?

PS: I attached the file in the original post

PS2: The corresponding table for the small dataset I provided should look like:

2003 2004 2005 2006

2003     6       3        2      0

2004     3       6        3      2

2005     2       3        7      1

2006     0       2        1      5

• ###### 3. Re: Number of common UIDs between years

Interesting problem

I came out with the following:

with a use of pretty simple table calculation:

• ###### 4. Re: Number of common UIDs between years

Hello,

That's it! Many thanks!

Quick followup question, which function should I use to give me the corresponding percentages (over the total number of records)?

Again, thank you for your time.

• ###### 5. Re: Number of common UIDs between years

The % of total would be yet another table calculation but differently addressed:

the same could be done with a use of LoD I believe.

I can't have 0 displayed instead of blank cell where the value is null though....

1 of 1 people found this helpful