how to sum up the prevoius rows?? loadscript help needed

[#1]

Hi all,

I have a scenario where i am totally stuck. I have the following kind of table

Period     Items     demands      Sum of next three periods (periods-Items)

1               a               5

1               r                7

1               c               8

1               d               9

2               w               5

2               a               4

2               c               4

3               d               7

3               a               8

3               r                0

4               a               8

4               f               9

4               d               8

4               r               1

to the above table, I want to add another column, which shows the sum of below three periods group by periods and items. into a fourth column. If it was just period and demands, then it would hav been easy by rangesum and manually add the previous three rows. However, in this case we do not know how many items does a period have and thus it should be dynamic.

Also remember, that each item from prevoius periods should be added into its relevant item.  for example.....the sum for next three periods for rows would be (a from period 2 and a from period 3 which would be 4+8 = 12)

Again, I want to do it in load script since there are some more calculations to be done on this. Please ask if unclear.

Thanks for your time

Arif

[nagaiank]

Here is a solution.

[#3]

hello,

Thanks for the reply but the solution is not what i want. why are there so many nulls. there should not be any null unless there is no element of the same category from below. Also the sum is not correct for the ones that have been calculated. Can you please check it.

Thanks

Arif

[nagaiank]

Hi Arif,

Probably I did not understand what you wanted.

I came up with a solution based on my understanding of your problem.

If you specify the result that you want for the data given in your original post, I may be able to come up with a better solution.

Kris.

[#5]

Period     Items     demands      Sum of current with next three periods (periods-Items)

1               a               5                                            25

1               r                7                                            8

1               c               8                                             12

1               d               9                                             24    

2               w               5                                              5        

2               a               4                                               20       

2               c               4                                                 

3               d               7                                                  

3               a               8                                            

3               r                0                                            

4               a               8                                            

4               f               9                                                 

4               d               8                                            

4               r               1                     

so basically, it is summing up of the demand for current item with the same items from below or next three periods (1,2,3.........) for every row in the table. If there are less than three periods left below, then it should not be calculated

[#6]

so basically it is the sum of demands for the current item within the current period + the sum of the same item in the next three period. this table has around 7100000 rows, and for each row it should be calculated

so for example.... period 1 item a demands should be summed with period 2  item a demands + period 3 item a demands+ period 4 item a demands , period 1 item r demands should be summed with period 2 item r demands+ period 3 item r demands + period 4 item r demands....and soooo on......if there are less than three periods below, then the calculation should be stopped. I have given only few periods above just for sample, but logically, in the big table, if we reach a sitation where there are less than three periods below, then the calculation should be stopped. like in the above case, only period 1 has three periods below. and no other period has three periods below it, so calculation should be done for period 1 only. however, the bigger table has like around 51 periods

Arif