3 Replies Latest reply on Apr 27, 2018 3:20 AM by Jim Dehner

    Ordering distinct records to a person

    Christopher Gibson

      I am trying to get an indication of the order of interactions with a person. I have records with person ID and the period in which the interaction happened, and I would like to track and compare what happens on, say, the 1st or 5th interactions across people, regardless of the actual date of the interaction.  Here is an illustration of what I am trying to get to (the 3rd column):

       

      PersonInteractionPeriodDistinctOrder
      AJan 20161
      AMar 20183
      AFeb 20172
      BJune 20183
      BMay 20172
      BMay 20172
      BApr 20171
      BMay 20172
      CSep 20143
      CAug 20142
      CJul 20141
      COct 2018

      4

       

      There is no limit to the number of interactions and there will be duplicates within the same period (as with Person B above having three records in May 2017, all marked with a '2'), and I need them to receive the same distinct order. I was experimenting with Rank(), but I am not having any luck. Has anyone done anything like this before, or does anyone have a lead for me?

       

      Thanks!

        • 1. Re: Ordering distinct records to a person
          Jim Dehner

          Christopher -

          it is not easy to provide a complete answer without have the dataset to work on

          YOu were going down a good track with rank but Index() would have been better - setup on person/date to restart every person

           

          but bear in mind you are now using table calculations - they can be used in conditional (if/then type)like if Index()=5) , statements but you will need to use other table calculations to further aggregate then

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          1 of 1 people found this helpful
          • 2. Re: Ordering distinct records to a person
            Christopher Gibson

            Hi Jim,

            Thank you for the feedback.  I will start looking at Index(), but I wanted to clarify something. I was originally thinking that I should create a calculated field for this so that I could analyze patterns across people based on that distinct order field. Are you saying that the only (or at least preferred) way to handle this would be to do so in the table calculations? I am still learning the limitations of each route.

             

            Thank you!

            • 3. Re: Ordering distinct records to a person
              Jim Dehner

              Actually I am saying that Rank and index are table calculations - they are on the bottom of the order of operation - but that's OK you just have to know that there are some types of calculated fields like LOD expressions that are higher on the order of operation that you will not be able to use with Index()

               

              Image result for tableau order of operation

               

              Nothing wrong with           if index()=5 then [something]  end    

               

              Jim