Rolling in script

[WalidChoukri]

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 

[Vegar]

Maybe you can do it like below :

LOAD *,

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

Resident Table;

DROP Table Table;

[WalidChoukri]

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.

[Vegar · Accepted solution]

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;

[jyothish8807]

Like this as well:

LOAD *,

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

Resident table

order by Customer , Date;

[WalidChoukri]

Thanks a lot.  It worked.