2 Replies Latest reply on Aug 10, 2016 7:27 PM by jenn.ng

    How to get difference across rows?


      I have a data set with info on when someone starts and stops an activity.

      I want to find the average time someone spends on each activity.


      the time variable is a unix timestamp so the difference will be time_event_close - time_event_open

      This is my sample dataset:


         1,255,1379312026,"open","A",2013-09-16 02:13:46

         1,255,1379312086,"close","A",2013-09-16 02:14:46

         1,182,1379312926,"open","A",2013-09-16 02:28:46

         1,182,1379313046,"close","A",2013-09-16 02:30:46

         1,81,1379314006,"open","A",2013-09-16 02:46:46


      This is what i have on Tableau:

      i just need to take:

      Average(unix timestamp of close - unix timestamp of open) for each item_id.


      Screen Shot 2016-08-09 at 5.42.14 PM.png




      If that is not possible, i could use date(hour) such that the open and close unix timestamps are side by side:


      Screen Shot 2016-08-09 at 6.04.47 PM.png


      However im not sure how i can get the difference still.


      Tried creating a calculated filed called delta and putting it on row shelf but didnt work.


      Screen Shot 2016-08-09 at 6.07.15 PM.png


      Work book attached.