Average of interval of days

[jduluc12]

Hi,

I have a data set like this.

Customer	Sales Date	Amount
Cust1	01/01/2017	100
Cust1	01/15/2017	200
Cust1	02/20/2017	100
Cust1	03/11/2017	450
Cust2	02/20/2017	120
Cust2	03/12/2017	450
Cust2	04/11/2017	560
Cust2	04/29/2017	780
Cust2	05/20/2017	120

What i want is to find the average of interval of sales dates for each customer.

like for Cust1 the interval of days between sales dates are

01/01 - 01/15 = 15

01/15 - 02/20 = 25

02/20 - 03/11 = 21

so the average would be

15 + 25 + 21 / 3 = 20.33

Is it possible?

Thanks

Jean

[Nicole-Smith]

FYI - The intervals you have above are not correct...there are 36 days between 1/15 and 2/20, and 19 between 2/20 and 3/11.

I would calculate the difference in the load script so you can just usage avg() on your charts:

Data:
LOAD *, IF(Customer = Previous(Customer), [Sales Date] - Previous([Sales Date]), 0) AS [Sales Date Diff];
LOAD * INLINE [
    Customer, Sales Date, Amount
    Cust1, 01/01/2017, 100
    Cust1, 01/15/2017, 200
    Cust1, 02/20/2017, 100
    Cust1, 03/11/2017, 450
    Cust2, 02/20/2017, 120
    Cust2, 03/12/2017, 450
    Cust2, 04/11/2017, 560
    Cust2, 04/29/2017, 780
    Cust2, 05/20/2017, 120
];

This gives us:

Customer	Sales Date	Amount	Sales Date Diff
Cust1	01/01/2017	100	0
Cust1	01/15/2017	200	14
Cust1	02/20/2017	100	36
Cust1	03/11/2017	450	19
Cust2	02/20/2017	120	0
Cust2	03/12/2017	450	20
Cust2	04/11/2017	560	30
Cust2	04/29/2017	780	18
Cust2	05/20/2017	120	21

And then you can just use avg([Sales Date Diff]) on a chart.

I've attached an example file doing the same.

[jduluc12]

The problem is that i do not have access to the load script.

But thanks anyway to help me out here.

[Nicole-Smith]

I think this expression should do the trick:

avg(aggr([Sales Date]-above([Sales Date]), Customer, [Sales Date]))

Example file is attached.

[jduluc12]

I am trying it out.