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

    Find distinct values between two date groups

      ContactIDClientsegMonth
      1Con1/1/2011
      2Test1/1/2011
      3Test1/1/2011
      1Con2/1/2011
      2Test2/1/2011
      3Test2/1/2011
      1Con3/1/2011
      3Test3/1/2011
      4Test3/1/2011
      1Con4/1/2011
      3Test4/1/2011
      4Test4/1/2011
      5Con4/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
          Shawn Wallwork

          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:

           

          CountD Table Calc.PNG

           

           

           

          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.

           

          --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

            ContactIDClientsegMonth
            1Con1/1/2011
            2Test1/1/2011
            3Test1/1/2011
            1Con2/1/2011
            2Test2/1/2011
            3Test2/1/2011
            1Con3/1/2011
            3Test3/1/2011
            4Test3/1/2011
            1Con4/1/2011
            3Test4/1/2011
            4Test4/1/2011
            5Con4/1/2011

            Outcomes:

            MonthscountD(contactID)Your SolutionWhat I wantComment
            Jan33All new
            Feb300No new contacts Added
            March3011 new contactadded
            April4111 new cintact added
            • 3. Re: Find distinct values between two date groups
              Shawn Wallwork

              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
                  Shawn Wallwork

                  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
                      Shawn Wallwork

                      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