Developers challenge calculating values over different rows

[andre_ficken]

I have a hard time trying to resolve this problem. Let me describe the issue: I need to build a table with the begin, end and mutations in stock levels for products over the last 24 periods starting with the current period. There is no history on stocklevels, that is why I need to create that. My approach has been to build a table with all products and join a table with 24 periods. Sort that on product, year (desc) and month (desc) and you can start calculating. I have the current stock at the moment of running the script. That will be my end position of the current period, substract the mutations and you will have the begin stock of the current period. I already came this far. In my table at this time I have begin, end and mutation for the current period and I have the mutations for all 24 periods. Now I need to complete the calculations for the remaining 23 periods. That means that as long as the productcode remains the same it need to copy the end position of the previous row as begin position on the current row and subtract the mutations to get the end position. I have been working on trying to resolve this for a while now using the peek function but run into a problem since in the script I have a table with my starting situation and I need to build a new table with the calculations. This means I need to use the source table for my test on product code and the mutations and from the table I am building I need to peek to the previous line for the end stock position. I cannot get that to work. My 'simple' question would be: Can anyone help me to resolve this?

I hope to hear from you soon on this topic.

[#2]

First, please don't write a block of text since it's hard to point out what is what.

Second, there are several intersecting functions to use over diagrams, based on either straight or pivot tables, such as before(), after(), top(), etc.

When you say periods, do you refer to months or days, or dynamic periods?

You say you have competed the beginning, mutations, and end for one stock for all 24 periods. At the same time you want to "complete the calculations for the remaining 23 periods"?

Is the "end stock position" supposed to be the "beginning stock position" for the next row or are the different rows simply different products?

Please clarify or attach an example.

//Jakob

[andre_ficken]

Sorry for the amount of text, but I want my problem to be clearly decribed.

The data is not going to be in a diagram straight away, but needs to be joined to other data and will have to be concatenated to. This is merely an inbetween table.

The periods are the last 24 months. This will move forward with each month.

My data at start for 1 product looks like this:

row item Year Month end mutations begin
1 P123 2010 1 1000 -75 1075
2 P123 2009 12 -10
3 P123 2009 11 50
4 P123 2009 10 -20
5 P123 2009 9 -15
6 P123 2009 8 50
7 P123 2009 7 -30
8 P123 2009 6 -10
9 P123 2009 5 -5
10 P123 2009 4 50

etc + 14 row of data up to 2008 january

Now for the calculations: on row 2 I need the begin of row 1, subtract the -10 to get an end of row 2 of 1085. Row 3 uses 1085 as a begin and substracts 50 for a 1035 begin stock level. And so forth.....

This example is for 1 product, but there are approx 9000 products in total. So only when the current row product is identical to the one on the previous row, this calculation needs to be done. The look of row 1 is there for each product.
Again a lot of text, but I hope this explains my previous text post.

Regs, André