1 of 1 people found this helpful
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:
It will add 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.
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
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
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.
ContactCount-SW.twbx.zip 12.5 KB
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?
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!)
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.
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:
If you figure this out let me know. I'm very interested in understanding how to do what you're trying to do. Thanks,