Average Time between Orders

renee12345 Member
edited September 2022 in Magic ETL

Stuck doing this in Magic ETL:

Trying to calculate the average time between orders. For each record in the DataSet, add a field showing the number of days since the previous order for that customer.

Customers have multiple orders so I can't use Max(date)



  • GrantSmith

    If you're looking to do this per customer you can utilize a Rank & Window tile and use the LAG function to get the prior record's value. You'll want to partition based on the customer ID. Then you can use a formula tile to calculate the difference between you current version and the lag version. Then put that into a Group By tile and calculate the average for each customer

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • shivaranjani
    shivaranjani Member
    edited April 2023

    Hi @GrantSmith can i get the detailed explanation for this average time between orders.