4 Replies Latest reply on Mar 7, 2018 4:26 PM by Zhouyi Zhang

    Calculating Average Time Between Records

    John Emery

      Hello Tableauers,

       

      I have an issue that I haven't been able to resolve on my own, so I'm turning to you for help.

       

      I have a data set that records each time a user creates a note in our system. It is structured as follows:

      • One record per note
      • Each record includes the timestamp, the user (not provided in this packaged workbook), and what account the note pertains to.

       

      What I need is to calculate the average amount of time that passes from one note to the next, with one caveat: if consecutive notes are the same, I do not want to calculate that difference.

       

      I have been able to calculate the time differences with no issue, and I have also calculated the time differences that I'm interested in, with the others being set at NULL. However, even with the NULL time difference values, the average that I'm getting is as if all time differences are being calculated.

       

      If that doesn't make sense, or is not clear, I have attached an Excel workbook with two sheets - one with the calculations how I want them, and one replicating what I'm seeing in Tableau. I have also attached a workbook so you may see my progress thus far.

       

      Any help would be very well appreciated. I feel like I'm very close here, but can't seem to get over this last hurdle.