Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

avg Interval, stop if not in line

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;

1 Reply
Not applicable
Author

Does nobody have a possible solution for this?