5 Replies Latest reply on Aug 17, 2016 4:29 PM by kettan

    Need help calculating time taken for every event

    Siddharth Nishar

      Hi,

       

      The structure of events' data for those using our App is as follows:

       

      Customer IDTimestamp (Epoch Time)Event ID
      11000000000E_1
      11000000002E_60
      21000000005E_2
      31000000023E_58
      21000000030E_4
      11000000035E_4
      31000000060E_111

       

      What I want is a calculated field that tells me how much time did every user spend on an event. For example, Customer 1 spent 2 seconds on E_1 because time(E_60)-(E_1)=2 seconds; however time spent by customer 2 on E_2 = time(E_4)-(E_2)=25 seconds. I realize that I will not have time for the last event and in this case, I am comfortable with a null value.

       

      I wish I could say that I have tried something and failed but I am completely new to Tableau and lost . I tried browsing through the forum to find something similar but anything I found relied on using the previous row which doesn't work here because the rows are not necessarily in the right order. The only sanity is the timestamp itself which is in ascending order.

       

      Thanks in advance for helping me

        • 1. Re: Need help calculating time taken for every event

          Hello Siddharth Nishar,

           

          I had a hard time understanding the issue however I think I finally get it! ... almost.

           

          Let's add some more concrete values with your examples:

          → Customer 1

          • Event E_1 occurred at 12:20:20
          • Event E_60 occurred at 12:20:22

          It means the event E_1 lasted for 2 seconds.

          Customer 2

          • Event E_2 occurred at 12:30:20
          • Event E_4 occurred at 12:30:45

          It means the event E_2 lasted for 25 seconds.

           

          You would need your [TimeStamp] and a new [EventLength] next to your [EventID]

          Here, in the examples, the [EventLength] would be 2s and 25s for E_1 and E_2 respectively.

          The calculation should be something more "mathematical" of this:

          [EventLength] of [EventID] n+1 = ([TimeStamp] of [EventID] n+1) - ([TimeStamp] of [EventID] n)

           

          From this point, I tried several things and failed.

           

          If someone could help us switch the light ON, that would be vastly appreciated!

          1 of 1 people found this helpful
          • 2. Re: Need help calculating time taken for every event
            Siddharth Nishar

            Hi Lenaic,

             

            You are indeed correct in your understanding of the problem! Thanks for

            giving it a shot!

             

             

             

            On Wed, Aug 10, 2016 at 8:24 PM, Lénaïc RIÉDINGER <

            • 3. Re: Need help calculating time taken for every event
              ahmed.hasnaoui

              If I understood your requirement correctly, then you can't solve this with your data how it is atm.

               

              An idea would be to create a timestamp when the customer leaves the app (onStop() in android e.g.).

              Then you have your timestamp for the end of the last event and you can calculate the time spent in that event.

              • 4. Re: Need help calculating time taken for every event
                kettan

                You could do something like this with a table calculation:

                 

                I wonder if there is a LoD (Level of Detail) calculation for the same?

                 

                If you would like to have this as a row-level measure rather than a table calculation,

                and if your data source is a relational database,

                you can do so with a correlated subquery column like this:

                 

                select "Customer ID"
                , "Timestamp (Epoch Time)"
                , "Event ID"
                , ( select min(t2."Timestamp (Epoch Time)") - t1."Timestamp (Epoch Time)"
                    from "Event data" t2
                    where t2."Customer ID" = t1."Customer ID"
                    and t2."Timestamp (Epoch Time)" > t1."Timestamp (Epoch Time)"
                   ) "Duration Seconds"
                from "Event data" t1
                

                 

                See more about this particular query in  SQL Fiddle.

                A template for generating underlying data for this SQL Fiddle query is attached below.

                 

                We can influence Tableau to feature correlated subqueries by up-voting:

                 

                18   Correlated Subquery

                 

                Your question is added to a collection of similar questions in mentioned idea.

                I don't remember, but maybe some of the referenced threads have a better or different solution than the one shared above.

                 

                Attached workbook version:  9.0

                • 5. Re: Need help calculating time taken for every event
                  kettan

                  An SQL Window Function could be used Instead of a correlated subquery column as shown below:

                   

                  select "Customer ID"
                  , "Timestamp (Epoch Time)"
                  , "Event ID"
                  , "Timestamp (Epoch Time)" - lag("Timestamp (Epoch Time)") over ( partition by "Customer ID" order by "Timestamp (Epoch Time)" ) "Duration Seconds"
                  from "Event data" t1
                  

                   

                  This query and its output (which is the same as earlier, of course) is available here:  SQL Fiddle

                   

                  It would be better if Tableau had built-in support of SQL Window Functions and therefore posted this idea earlier today:

                   

                  SQL Window Functions

                  .