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.
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.
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.
Do you have a ready-made database or do you start everything from scratch?
If you start from scratch, you should work with this method.
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?
Here’s a slightly easier spin on my second suggestion above.
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.