topic Rolling in script in QlikView

Rolling in script

WalidChoukri — Mon, 11 Jan 2021 15:04:15 GMT

Hello,

I'm having this problem with my code, i have a table as you can see below:

Table

LOAD * INLINE [
Date, Customer, Sales
01/07/2012, A, 100
01/08/2012, A, 100
01/09/2012, A, 100
01/10/2012, A, 100

01/04/2012, B, 50
01/05/2012, B, 50
01/06/2012, B, 50
01/07/2012, B, 50

01/11/2012, A, 100
01/12/2012, A, 100
01/01/2013, A, 100
01/02/2013, A, 100

01/10/2012, B, 50
01/11/2012, B, 50
01/12/2012, B, 50
01/01/2013, B, 50
];

I Want to get a table with Rolling sales where the results are like this :

If i select Customer A for example :

Date, Customer, Sales, Rolling Sales
01/07/2012, A, 100, 100
01/08/2012, A, 100, 200
01/09/2012, A, 100, 300
01/10/2012, A, 100, 400
01/11/2012, A, 100, 500
01/12/2012, A, 100, 600
01/01/2013, A, 100, 700
01/02/2013, A, 100, 800

I tried the code below:

LOAD *,

If(Customer = previous(Customer), RangeSum(Sales,Peek('Rolling')), Sales) as Rolling
Resident Table
;
DROP Table Table;

Any suggestions or other ways to achieve that result is much appreciated, Thank you

Re: Rolling in script

Vegar — Mon, 11 Jan 2021 17:36:27 GMT

Maybe you can do it like below :

LOAD *,

If(Customer = previous(Customer), Peek('Rolling'), 0) +Sales as Rolling

Resident Table;

DROP Table Table;

Re: Rolling in script

WalidChoukri — Tue, 12 Jan 2021 08:27:55 GMT

Thanks for your reply, in fact your expression would work just the same as mine,

the problem i'm facing is how to get the last rolling sales of the customer A, as the loading order alternate between Customer A and B, the system re calculate from scratch, i don't know if my description is clear enough, but what i need is the moment the program handle a different customer it'll try to search if there's any previous records of the same customer if yes it'll add the latest rolling sales if not it'll start over.

Again thank you so much.

Re: Rolling in script

Vegar — Tue, 12 Jan 2021 11:56:20 GMT

To use this approach you need to control the load order of your data. Try adding an order by like below.

LOAD *,
If([Customer] = peek('Customer'), Peek('Rolling'), 0) + [Sales] as Rolling
Resident Table
Order by [Customer], [Date] ;

DROP Table Table;

Re: Rolling in script

jyothish8807 — Tue, 12 Jan 2021 12:18:14 GMT

Like this as well:

LOAD *,

If(Customer <> previous(Customer),Sales, Peek(Rolling)+Sales) as Rolling

Resident table

order by Customer , Date;

Re: Rolling in script

WalidChoukri — Tue, 12 Jan 2021 12:26:57 GMT

Thanks a lot. It worked.