Average Time between Row Entries

Ask for Help
1

I have a sheet with form submissions that include a date column, a customer row ID and a relation to this data in the “customer” table. Each customer will have multiple rows, each with timestamp date. I’m looking to have a column that computes the average time between row entries, for each customer, rounded to days. Is this possible in glide or do I need to do this in sheets?

I already have a column that counts the current days since last entry, but i’m already 1700 entries deep and hundreds of customers, so I can’t back-calculate each one easily. Going forward I know I could just submit the “days since last entry” as another piece of data in another column and then just average that data itself, but if it can stay as a reliable formula, that would be best.

2

yes, use if-else column to extract sign-in user records… then use the rollup column to get the average… you might do some math with dates to get durations

3

Sorry, I forgot to mention, these are form submissions.

4

no different… data is data

5

My idea is like this:

  • Add a rowID column in your submissions table.
  • Create a multiple self-relation from the customer ID column in the submissions table, to itself.
  • Return a lookup on top of that self-relation, returning all the rowIDs.
  • Use a “find element index” column to return the index of each row, within each own customer’s group.

You would get the index column like this.

Date Customer ID Index
22 July 2022 A1 0
23 July 2022 A2 0
24 July 2022 A1 1

Then, add a math column to calculate the “previous index”. It would be Index minus 1.

Date Customer ID Index Previous Index
22 July 2022 A1 0 -1
23 July 2022 A2 0 -1
24 July 2022 A1 1 0

Create a template to join the customer ID and the previous index so it looks like “A1 || -1”, “A1 || 0” etc.

Create a template to join the customer ID and the current index (Index column).

Create a single relation from the previous index template column, to the current index template column.

Add a lookup column on top of the relation above, to return the “previous timestamp”.

Add a “Date Difference” column to calculate the difference between each row’s timestamp and the previous timestamp.

Add a rollup to get the average of “Date Difference”.

Not sure it would generate a bit of a bug for the first entry (since there’s nothing before it), so let me know how it goes.

4 Likes
6

I’m stunned that you did this. I’ve spent 4+ hours racking my brain with index, match and lookup arrays in sheets. This was so elegantly explained! Thank you! :star_struck:

1 Like
7

Glad it worked for you! Let us know if you have any other questions.

8

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.