# Calculate the average between database of dates, right approach?
**URL:** https://community.glideapps.com/t/calculate-the-average-between-database-of-dates-right-approach/69163
**Category:** Ask for Help
**Created:** [January 1, 2024, 7:57pm UTC](https://community.glideapps.com/t/calculate-the-average-between-database-of-dates-right-approach/69163 "2024-01-01T19:57:49Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@ohad](https://community.glideapps.com/u/ohad)
#### Post date: [January 1, 2024, 7:57pm UTC](https://community.glideapps.com/t/calculate-the-average-between-database-of-dates-right-approach/69163/1 "2024-01-01T19:57:49Z")
Hi all,
I have X users that submits 1 new row every 1-3 days.
I would like to calculate the average days between each submit,
For example,
User A submits new row every 2 days, sometimes 3 sometimes 4 sometimes every day , total submissions is 150 so = \> this user’s average is: \_\_\_
DATA:
11/29/23
11/27/23
11/26/23
11/24/23
11/23/23
on average the user submits every: 2 days.
What is the right approach to is with Glide?
Thank you.
---
### Author:  [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)
#### Post date: [January 1, 2024, 8:36pm UTC](https://community.glideapps.com/t/calculate-the-average-between-database-of-dates-right-approach/69163/2 "2024-01-01T20:36:14Z")
The math column let’s you calculate the the difference between dates, so that part is easy, and then a Rollup column could calculate the average. The tricky part is figuring out how to get the prior date and the current date in the same row so you can calculate the difference in days.
There’s several ways to approach it. One is to aquire the latest date, (maybe into the user profile row) and write that date into the new row along with the new date. A much more complicated method is to automatically number your rows, subtract 1 from that row number, and create a relation/lookup to retrieve the date from the prior row. For simplicity, I think I would go with the first method.
---
### Author:  [@ohad](https://community.glideapps.com/u/ohad)
#### Post date: [January 1, 2024, 8:56pm UTC](https://community.glideapps.com/t/calculate-the-average-between-database-of-dates-right-approach/69163/3 "2024-01-01T20:56:16Z")
Thank you, I’ll explain little more to clerify my need.
Let’s say each of my users has 500 rows, starting from 2022 until now.
How do I calculate the distance between days = between row to row?
?
For example last row is today, 2nd last was 2 days ago, 3rd last was 5 days ago… 1st row was in 2022.
---
### Author:  [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)
#### Post date: [January 2, 2024, 1:36am UTC](https://community.glideapps.com/t/calculate-the-average-between-database-of-dates-right-approach/69163/4 "2024-01-02T01:36:08Z")
Do you have a ready-made database or do you start everything from scratch?
> [@Jeff\_Hager](#):
>
> One is to aquire the latest date, (maybe into the user profile row) and write that date into the new row along with the new date.
If you start from scratch, you should work with this method.
---
### Author:  [@ohad](https://community.glideapps.com/u/ohad)
#### Post date: [January 2, 2024, 4:31pm UTC](https://community.glideapps.com/t/calculate-the-average-between-database-of-dates-right-approach/69163/5 "2024-01-02T16:31:15Z")
I understand what you meant , it is a good idea and I did it for future database analyze.
But yes, I have ready-made database from the past year. What do you suggest about it?
---
### Author:  [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)
#### Post date: [January 2, 2024, 4:53pm UTC](https://community.glideapps.com/t/calculate-the-average-between-database-of-dates-right-approach/69163/6 "2024-01-02T16:53:09Z")
Here’s a slightly easier spin on my second suggestion above.
> [@Creating a formula to maintain a running balance](https://community.glideapps.com/t/creating-a-formula-to-maintain-a-running-balance/68973/2):
>
> The query column can help you with this. The video below walks through setting it up.
The concept would be similar as far as creating row indexes. As for the query, I would change the compare to look for indexes ‘less than’ the current row index instead of ‘less than or equal’. I would add a second condition to only match the user to their own rows (maybe comparing emails). I would then change the sorting by date in reverse order and set it to return only 1 row. With that query set up, then you can create a single value column that retrieves the date value from through the query. You should send up with a date from the previous entry for that user that you can use with a math column and rollup to get the average.