4 Replies Latest reply on Apr 29, 2019 1:32 AM by Andrzej Kostewicz

    Calculating time difference between X and the earliest Y if Z

    Andrzej Kostewicz

      Ahoj

       

      I need to calculate time differences between the moment of user registration (X) and the moment of service first usage (Y) when each user has a unique ID (Z). My problem is that the database counts all usages of the service. So when I first use the service, I get:

       

      Z (user_ID) - "Pink Unicorn"

      X (registration_time) - 09/06/2016

      Y (usage_session) - 12/01/2018

       

      Nevertheless, if I use the service for the second time, then I get:

       

      Z (ID) - "Pink Unicorn"

      X (registration_time) - 09/06/2016

      Y (usage_session) - 12/01/2018

      Y (usage_session) - 17/04/2019

       

      etc. etc.

       

      I calculate the time difference between X and Y using the following formula:

       

      "STR(CEILING(DATEDIFF('minute',[registration_time],[usage_session])/60)-1)+"."+STR(DATEDIFF('minute',[registration_time],[usage_session]) -((CEILING(DATEDIFF('minute',[registration_time],[usage_session])/60)-1) * 60 ))"

       

      Any ideas on how to adjust the formula so that it will take only THE EARLIEST "Y (usage_session)" if there are multiple "Y (usage_session)"? All my attempts were wrong so far

       

      All the best to all of you!!

        • 1. Re: Calculating time difference between X and the earliest Y if Z
          Jonathan Drummey

          Hi,

           

          I'm not clear on your exact data structure. Assuming that each usage session is generating an additional row in the data source then the usual method would be to do an aggregation step with a Group By on the user Z getting the Min of usage session Y. Depending on how your data is set up you might be able to use the aggregated results as-is or might have to join them back to the original non-aggregated data. In the latter case the way we can do that in Prep is to add a branch step to the data and then join the aggregate results to the branch.

           

          Jonathan

          • 2. Re: Calculating time difference between X and the earliest Y if Z
            Andrzej Kostewicz

            Dear Jonathan,

             

            You are a genius!! I've been trying so many complicated approaches to amend the formula while the solution was that simple

             

            One more question: any idea on how to calculate the time difference between the two dates of service usage of the same user if there are more than 2 values?

             

            For example, if I have a user:

             

            Z (ID) - "Pink Unicorn"

            Y (usage_session) - 12/01/2018

            Y (usage_session) - 17/04/2019

            Y (usage_session) - 24/04/2019

             

            Then I would like to have a difference between the first two Ys and then another difference between the 2nd and the 3rd Y   All the dates are in the same column. Hence, the formula should only take two consecutive values from that column

             

            Super thanks!!

             

             

            • 3. Re: Calculating time difference between X and the earliest Y if Z
              Jonathan Drummey

              Hi,

               

              This ask changes things because the goal is to do an inter-record comparison and that isn't well-supported by Tableau Prep. There are two methods for this:

               

              1) Set up a self join using a condition where date < date and then filter out all the extra rows. This gets you a data set with the next dates as additional columns and then we can do a DATEDIFF() between them. I built this in the attached .TFL file. The one challenge with this is that if there's some condition where you could have multiple entries on the same day you have to be careful of the joins.

               

              2) Generate a row number that identifies each row in the sequence for each user/customer, then do a self-join on the row number = row number - 1. This gets around the multiple entries on the same day problem because you've explicitly identified the sequence. Tableau Prep doesn't yet support row numbering tho users have come up with workarounds.

               

              Jonathan

              • 4. Re: Calculating time difference between X and the earliest Y if Z
                Andrzej Kostewicz

                Hey again,

                 

                Ok. Super thanks for your hyper-qualified help I will find a way to calculate this in our own code