Generate stock for every day

[Anonymous]

There must be someone who has done this before.

I need to create stock information for ever date, in the script, but I only got the begin amount and the changes.

I think it is easiest to explane with an example.
Here is an example of what I have and want:

I have the changes

Product	30-10-2012	31-10-2012	1-11-2012	2-11-2012	3-11-2012	4-11-2012	5-11-2012	6-11-2012
AAA	20	1	-1		33		5	6
AAB
AAC	100	3
BBB	1	3	-10	5	0	22	0	45
BBC	23	3			-2		-6
CCC		3	4			-5		12

This is the result I am looking for:

Product	30-10-2012	31-10-2012	1-11-2012	2-11-2012	3-11-2012	4-11-2012	5-11-2012	6-11-2012
AAA	20	21	20	20	55	55	60	66
AAB	0	0	0	0	0	0	0	0
AAC	100	103	103	103	103	103	103	103
BBB	1	4	-6	1	1	23	23	48
BBC	23	26	26	26	24	24	18	18
CCC	0	3	7	7	7	2	2	14

So for every day I want to have the amount that is in stock based on the changes.

What is the way to do this in the script?

Thanks,

Dennis.

[thomas_skariah]

Hi Dennis,

You can try this code in script

If(Date > Previous(Date), RangeSum(Data, Peek('DateNew')), Data) AS DateNew

Regards,

Tom

[Anonymous]

Thanks Tom , but that doesn't get any results....

[Anonymous]

I am a bit closer now I think because I have sorted the data in the right way now.

What I got now is data like this:

Product ID	date	Amount
AA	30-10-2012	4
AA	31-10-2012
AA	1-11-2012
AA	2-11-2012	6
AA	3-11-2012
AA	4-11-2012	-3
AA	5-11-2012
AA	6-11-2012
BB	30-10-2012	1
BB	31-10-2012
BB	1-11-2012	100
BB	2-11-2012
BB	3-11-2012
BB	4-11-2012	-25
BB	5-11-2012
BB	6-11-2012	3

What I want is:

Product ID	date	Amount
AA	30-10-2012	4
AA	31-10-2012	4
AA	1-11-2012	4
AA	2-11-2012	10
AA	3-11-2012	10
AA	4-11-2012	7
AA	5-11-2012	7
AA	6-11-2012	7
BB	30-10-2012	1
BB	31-10-2012	1
BB	1-11-2012	101
BB	2-11-2012	101
BB	3-11-2012	101
BB	4-11-2012	76
BB	5-11-2012	76
BB	6-11-2012	79

Any suggestions?

Thanks,

Dennis.

[Gysbert_Wassenaar]

You can use peek to calculate the Amount field

LOAD [Product ID],date,

if(rowno()=1,

    Amount,

    if([Product ID] = peek([Product ID]),

        if(len(Amount)=0,

            peek(Amount),

            peek(Amount)+Amount),

        Amount)

    ) as Amount

INLINE [

    Product ID, date, Amount

    AA, 30-10-2012, 4

    AA, 31-10-2012,

    AA, 01-11-2012,

    AA, 02-11-2012, 6

    AA, 03-11-2012,

    AA, 04-11-2012, -3

    AA, 05-11-2012,

    AA, 06-11-2012,

    BB, 30-10-2012, 1

    BB, 31-10-2012,

    BB, 01-11-2012, 100

    BB, 02-11-2012,

    BB, 03-11-2012,

    BB, 04-11-2012, -25

    BB, 05-11-2012,

    BB, 06-11-2012, 3

];

[Anonymous]

I think I got it with Toms solution:

IF(ProductID =Previous(ProductID) , RangeSum(Amount, Peek('Stock') ) , Amount)  as Stock

What do you think?

[Anonymous]

I am goin to try this too.

Thanks Gysbert.