Calculating the average time difference between orders,

[SophieRawcliffe]

Hi community, 

My data has the customer ID and then the date they made a purchase. I want to work out the average time between purchases per customer but not sure where to start. Some customers have 1 purchase, some have a lot. 

Thanks for any help 

CustomerID	Date of transaction
10118500	11/06/2024
10118500	22/05/2023
10118500	02/05/2024
10118500	01/01/2023
10118500	12/12/2023
10189549	11/09/2023
10189549	07/07/2024
10915523	11/12/2023
10049535	04/05/2022
10049535	05/05/2022
10049535	06/05/2022
10049535	23/04/2024

[ScottLewis]

Attached should give you something to start with.

General Pattern for this sort of thing:

1. Format the data. Usually this means wrangling date formats.

2. Sort by group fields and then the thing you're comparing (in this case, Customer is a group, Date is the compare)

3. Use a multi-row field to get the change in the comparative, setting missing values to NULL

4. Use Summation to get the average. NULL after summation indicates a single entry, in this case a customer with one purchase.

[griffinwelsh · Accepted solution]

@SophieRawcliffe take a look at the attached solution. You need to do the following:

1. Convert your date string to date time

2. Sort on customer id and date

3. Use the multi-row formula to calculate difference between dates. (make sure to group by customer)

4. Filter out nulls

5. Use the summarize tool to obtain the average time between orders

6. Find customers with only one order and union them back into the final result (optional)