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

# Calculating Average Time Between Records

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.

• ###### 1. Re: Calculating Average Time Between Records

Hi, John

Please find my solution attached as well as screenshot below

Hope this helps

ZZ

• ###### 2. Re: Calculating Average Time Between Records

And the avg as well

ZZ

• ###### 3. Re: Calculating Average Time Between Records

ZZ,

Thank you very much for this. It looks like I was close, but was missing a LOOKUP function in one of my calculations. From you answer I was able to easily go from the sample I provided to my real dataset, with many users.

- John

• ###### 4. Re: Calculating Average Time Between Records

You are welcome and glad to help

ZZ