avg Interval, stop if not in line

Thu, 12 Sep 2013 13:22:48 GMT

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;

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;

Re: avg Interval, stop if not in line

Tue, 01 Oct 2013 09:56:40 GMT

Does nobody have a possible solution for this?

topic Re: avg Interval, stop if not in line in QlikView

avg Interval, stop if not in line

Re: avg Interval, stop if not in line