1 Reply Latest reply on Sep 10, 2018 2:51 PM by Joe Oppelt

    Average time between records

    vickram.jain.0

      I have a dataset as below & would like to calculate the average time between records:

       

      user_idcreated
      12018-09-10 12:00:00
      12018-09-10 12:01:01
      12018-09-10 12:01:02
      22018-09-10 13:00:00
      22018-09-10 13:05:00
      22018-09-10 13:07:30
      32018-09-10 13:00:00
      32018-09-10 13:00:00
      32018-09-10 13:00:01

       

      So the resulting chart from the above should something similar to:

       

      user_idaverage
      11 sec
      2225 secs
      30.5 secs

       

      There have been a few posts on the forums detailing similar needs, but I haven't been able to get those working. Any help would be appreciated!

        • 1. Re: Average time between records
          Joe Oppelt

          You might need to upload a workbook if you can't get this working.  But here's what you want to do.

           

          DATEDIFF('second', LOOKUP(ATTR([Created]),-1) , ATTR([Created])   )

           

          this will tell you the difference in seconds between the current row and the prior row.  This is a table calc, and you will want to set your table calc setting to restart every [user_id].    (You don't want the calc comparing the first row of a user ID with the last row of the prior userID.)

           

          Then you want to do a window_average of those values, also restarting every user_id.

           

          You'll end up with each user_id's average value on each row within that user_id.  You'll then want to filter to display only the first row of each user_id.  that will give you the final value you're looking for.