3 Replies Latest reply on Jan 15, 2015 8:19 PM by Chris McClellan

    Calculate the date time difference between two rows

    Amer Ghanem

      Hello,

       

      I have data for a game where I am capturing the session id, event type, and time. a player can have multiple events in one session. In my task I have to determine the duration of a round in the game. We are capturing an event called round start, and an event called end route end. What I am trying to do is identify the paris of round start and round end, and calculate the difference in minutes to determine the duration of the round, and then calculated the average round duration for all my sessions.

       

      I am attaching a report with a sample data and I have having a couple of issues. I have grouped events by Session, then displayed them by time.

      1- I am trying to determine the event type of the next row by using index() and lookup(). For some reason that is not working and I thought the calculation is straight forward.

       

      2- once I do that, I am then trying to determine the pairs so I can calculated the time difference. There is also a calculation that looks up the time of the next event and tries to do a datediff. But it also fails.

       

      Any guidance would be greatly appreciated.

       

      Thanks

        • 1. Re: Calculate the date time difference between two rows
          Chris McClellan

          Sounds like a classic case of using Custom SQL to model the data before you use it in Tableau - is the data in a SQL database before you pull it into Excel ?

           

          Also, the data looks pretty dirty (times missing, session IDs missing).

           

          Finally, have a look at this and see if it helps FAQ:  Open & Close Dates

          • 2. Re: Calculate the date time difference between two rows
            Amer Ghanem

            This data is a sample from the early loggings. It is much better now and I will be applying the time calculation to the later dates. I chose this because it was smaller.

             

            I tried to do it in SQL but my sql skills are not great, I thought it would be easier to do it in Tableau.

             

            Thanks

            • 3. Re: Calculate the date time difference between two rows
              Chris McClellan

              I did this in Tableau:

               

              select

              RS.[Session ID],

              RS.[Event Time] as StartTime,

              RE.[EndTime] as EndTime,

              RE.[EndTime] - RS.[Event Time] as duration

              from [Sheet1$] as RS

              ,

              (select [Session ID],[Event Time] as [EndTime] from [Sheet1$] where [Event Type] = "Round End") as RE

              where RS.[Session ID] = RE.[Session ID]

              and RS.[Event Type] = "Round Start"

               

              BUT .... you have duplicate Session ID values, so I don't think that will work 100%, you need to do a correlated subquery and the SQL engine in Tableau doesn't understand that syntax.

               

              You will need to test something like this to make it work perfectly

               

              select

              RS.[Session ID],

              RS.[Event Time] as StartTime,

              RE.[EndTime] as EndTime,

              RE.[EndTime] - RS.[Event Time] as duration

              from [Sheet1$] as RS

              ,

              (select [Session ID],min([Event Time]) as [EndTime] from [Sheet1$] where [Event Type] = "Round End"

              where [Event Time] > RS.[Event Time]

              ) as RE

              where RS.[Session ID] = RE.[Session ID]

              and RS.[Event Type] = "Round Start"

               

               

              in this second query I've highlighted the changes.  I haven't tested this, so you might need to play with it a bit.