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

    How to get difference across rows?

    jenn.ng

      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:

         "person_id","item_id","time","event","type","date"

         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.