Forum Discussion

Milan_TBC's avatar
Milan_TBC
Frequent Visitor
3 years ago
Solved

Average Customer Return Time

Hi,

I want to calculate the average return time of my customers over time. 
I have a table with general data such as Cust_ID, and order_date per order. 
Right now i have general calculations based on first_order and last_order, however this doesn't cover all averages, since some customers have 10+ orders. 

 

Is there a way to calculate the average time between orders? Using the regular DATE commands (DATEDIFF, DATESBETWEEN, etc,), i run into problems since most of them only accept two dates, not more. 

 

Thanks in advance!

  • amitchandak's avatar
    amitchandak
    3 years ago

    Milan_TBC , A new column

     

    Diff =

    var _max  = maxx(filter(Table, [Customer] = earlier([Customer]) && [Order date] < earlier({order date]) ) , [Order Date])

    return

    datediff(_max, [Order date], day)

     

    Then you can take AVg of this column

     

     

    Or move this in a Meausure

     

    AverageX(Table,

    var _max  = maxx(filter(Table, [Customer] = earlier([Customer]) && [Order date] < earlier({order date]) ) , [Order Date])

    return

    datediff(_max, [Order date], day) )

     

    Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Milan_TBC , A new column

     

    Diff =

    var _max  = maxx(filter(Table, [Customer] = earlier([Customer]) && [Order date] < earlier({order date]) ) , [Order Date])

    return

    datediff(_max, [Order date], day)

     

    Then you can take AVg of this column

     

     

    Or move this in a Meausure

     

    AverageX(Table,

    var _max  = maxx(filter(Table, [Customer] = earlier([Customer]) && [Order date] < earlier({order date]) ) , [Order Date])

    return

    datediff(_max, [Order date], day) )

     

    Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8