avg Interval, stop if not in line

[#1]

Hi all,

I have a script that calculates the interval per customer for a specific product.

In my original DB it's possible that Product X has multiple rows on a date with a specific client.

Because of this, I load distinct sales.

For example, Client A has bought Product X on different dates. On the 12th of July it has bought it twice.

My table with distinct looks like this:

Customer,  Product, Date

A, X, 01-07-2011

A, X, 12-07-2013

A, X, 19-08-2013

B, X, 13-07-2013

etc

This is working correct.

As you can see, the first sale to customer A was in 2011.

During 2012 this customer wasn't a customer to our company.

My interval calculates 19-8-2013  minus 12-07-2013

and 12-07-2013 minus 01-07-2011. etc.

Because I calculate an average Interval during the last 3 and 10 orders, Customer A gets a wrong interval average.

Is it possible that the interval stops calculating when the peek(interval) number isn't in line with the other intervals from that customer?

Why am I doing this?
I want to predict the next ordering date and I want to see if a customer buys more or less product.

So it is possible that my last 10 and last 3 interval stops if a value is greater then, for example 200%, then the last interval?

My current code looks as following:

Code

IntervalPRODUCTX: Directory; LOAD distinct      Customer,      Artikelidentification,     DATE#(DeliveryDate,'D.M.YYYY') as DeliveryDate, FROM [FileLocation] (biff, embedded labels, table is [Sheet1$]) where Artikelidentification= 'PRODUCT X'; join (IntervalPRODUCTX) Load Customer, DeliveryDate ,if(peek(Customer)=Customer,-peek(DeliveryDate)+DeliveryDate) as Interval resident IntervalPRODUCTX where Artikelidentification= 'PRODUCT X' order by Customer, DeliveryDate; join(IntervalPRODUCTX) Load Customer, DeliveryDate, if(peek(Customer)=Customer, numsum(peek('Counter'),1),1) As Counter  Resident IntervalPRODUCTX where Artikelidentification= 'PRODUCT X' order by Customer, DeliveryDate; join(IntervalPRODUCTX) load Customer, Counter, Interval,        //Counter 3 if(Counter>=3,RangeAvg(Interval,Peek('Interval'),Peek('Interval',-2)),Interval) as Rolling3Avg,        //Counter 10 if(Counter>13,RangeAvg(Peek('Interval',-3),Peek('Interval',-4),Peek('Interval',-5),Peek('Interval',-6),Peek('Interval',-7),Peek('Interval',-8),Peek('Interval',-9),Peek('Interval',-10),Peek('Interval',-11),Peek('Interval',-12)), if(Counter=13,RangeAvg(Peek('Interval',-3),Peek('Interval',-4),Peek('Interval',-5),Peek('Interval',-6),Peek('Interval',-7),Peek('Interval',-8),Peek('Interval',-9),Peek('Interval',-10),Peek('Interval',-11),Peek('Interval',-12)),'N/B') ) as Rolling10Avg  //Calculating the average for the 10 deliveries before the Rolling5Avg resident IntervalPRODUCTX where Artikelidentification= 'PRODUCT X' order by Customer, Counter;

[#2]

Does nobody have a possible solution for this?