Hello :wave: everyone!
Wonder if anyone can help me. Background is that we run a startup where trial users do a free session then come back and do a paid session. I want to understand:
a) the average time between the free session and the first paid session
b) then the time between subsequent paid sessions
So I’ve got a single lookup field with a list of dates in it:
(10/1/2020, 8/24/2020, 9/28/2020, 10/8/2020, 9/24/2020, 12/14/2020, 10/5/2020, 10/27/2020, 10/18/2020, 11/19/2020, 8/31/2020, 9/7/2020, 10/15/2020, 10/12/2020, 11/5/2020, 10/19/2020, 11/12/2020, 11/9/2020, 10/26/2020, 11/10/2020, 12/10/2020, 9/21/2020, 9/14/2020, 10/29/2020, 10/22/2020, 10/31/2020, 11/2/2020, 11/5/2020, 11/21/2020, 12/1/2020, 1/19/2021, 1/26/2021)
I’m struggling to find a way to use DATETIME_DIFF correctly to do this as it seems to assume that dates are all in separate fields, which obviously mine are not.
Does anyone have any ideas on how to solve it?
Thanks so much in advance!
You want to perform calculations across rows in your linked table. Airtable does not have good support for this.
You could have each record in your linked table also link to the next record in the series and calculate the durations that way. Then you can use rollup fields on the durations. However, that is a lot of linking to maintain.
You could use a script. The script could either manage the links in the above scenario so that formula fields and roll ups do the calculations. Or the script could do the calculations directly.
When it comes to scripts, do you want to invest time or money? Do you want to learn to code, or do you just want working code?
Some people do not want to learn scripting, but they have budgets to hire someone. If this is your situation, I (and others) are available for hire. If you are interested in hiring me, you can see my process or book an appointment for more information.
Some people are not interested in learning scripting and also do not have budgets. This is a difficult situation, and you need to decide how important the feature is or get creative coming up with a different solution (which can also take time or money).
Good luck in your decision!