7 Replies Latest reply on Feb 8, 2012 4:43 AM by Shawn Wallwork

# Find distinct values between two date groups

 ContactID Clientseg Month 1 Con 1/1/2011 2 Test 1/1/2011 3 Test 1/1/2011 1 Con 2/1/2011 2 Test 2/1/2011 3 Test 2/1/2011 1 Con 3/1/2011 3 Test 3/1/2011 4 Test 3/1/2011 1 Con 4/1/2011 3 Test 4/1/2011 4 Test 4/1/2011 5 Con 4/1/2011

Above is some test data and below is an example of where I at.

Curently I have countd(contactid)  and then group those by month. What I need is the  total  number of different contact IDs between two month groups.

For example, lets say in January we have contact A,B,C and in Feb. we have contacts A,C,D. So now for Feb I want it to display 1 because we added D and it is not in January. Any ideas on how i would go about doing that

• ###### 1. Re: Find distinct values between two date groups

Randi, if I understand what you want, all you need to do is rt-click that green pill you have on the text shelf and add a table calculation that looks like this:

ZN(COUNTD([ContactID])) - LOOKUP(ZN(COUNTD([ContactID])), -1)

Which gives you the month-to-month comparison it seems like you're looking for. If you need CountD from the very beginning of you data, that's a whole different deal.

--Shawn

1 of 1 people found this helpful
• ###### 2. Re: Find distinct values between two date groups

Thank you Shawn for responding but that doesn't find the distinct number of contacts between the two month groups. It's only found the distinct number from the total contact segment for each much and subtracted it.

Here's an example:

Here's the sample data set

 ContactID Clientseg Month 1 Con 1/1/2011 2 Test 1/1/2011 3 Test 1/1/2011 1 Con 2/1/2011 2 Test 2/1/2011 3 Test 2/1/2011 1 Con 3/1/2011 3 Test 3/1/2011 4 Test 3/1/2011 1 Con 4/1/2011 3 Test 4/1/2011 4 Test 4/1/2011 5 Con 4/1/2011

Outcomes:

 Months countD(contactID) Your Solution What I want Comment Jan 3 3 All new Feb 3 0 0 No new contacts Added March 3 0 1 1 new contactadded April 4 1 1 1 new cintact added
• ###### 3. Re: Find distinct values between two date groups

Randi, sorry for the delay, work project interrupted (I hate it when that happens ) OK, see attached and please read notes in the field calc. If these assumptions aren't your real situation, it can be adjusted as needed.

--Shawn

• ###### 4. Re: Find distinct values between two date groups

Haha!

Thank you so much! This looks really close to what I'm looking for. Only question: What happens if the contact Id is not sequential?

Could we just remove max() and then do a nested IFF and subtract contactID from contactID. If 0 then dont count else +1?

• ###### 5. Re: Find distinct values between two date groups

Hi Randi. Glad we're getting close. I'm working on my second glass of wine so evaluating 'remove max()' & 'nested IIF' sounds a little ambitious. But first thing in the a.m. I'll give it a look. There is a whole area of Tableau dealing with looking up individual records and doing calculations from there that I'm totally unfamiliar with, which might be your ultimate solution. If Joe ever gets a break from his obviously full schedule, I'm sure he'll have your solution.

One note: Create a data set that represents ALL possible permutations of the results you are looking for. That way we can address your exact situation. (Do this and I'm pretty sure that either Joe or Richard will jump in with your answer.)

Cheers (and I mean that in the pub sense!)

--Shawn

• ###### 6. Re: Find distinct values between two date groups

I hope to be near my first in approximately 10 min.

Unfortunately this will be running off of a live DB so the ID's will vary. Each value will mostly likely be the same length but in all likely hood they will not be consecutive numbers.

That's why I was thinking if we could subtract the contact ID's from each other across group months. It'll either be 0 or a number > then zero. That would be a costly operation as you would have nested loops and I'm not sure Tableau can do that?

Also, do you know where I can find info on looking up individual records and doing calculations on them? I've tried search and I think I'm just not coming up with intuitive search criteria.

Thanks for all the help. Look for to seeing what you and or Joe and or Richard come up with.

• ###### 7. Re: Find distinct values between two date groups

Randi, I worked on this a bit yesterday, but I still can't get my brain around how table cable calculations and partitions work/interact. As to your last questions here are a few links to discussions that will get you started:

http://www.tableausoftware.com/search/community/LOOKUP()

http://www.tableausoftware.com/search/community/FIRST%2528%2529%253D%253D0

http://www.tableausoftware.com/search/community/partitioning

If you figure this out let me know. I'm very interested in understanding how to do what you're trying to do. Thanks,

--Shawn