topic Re: how to sum up the prevoius rows?? loadscript help needed in QlikView

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

Mon, 13 Jun 2011 21:28:17 GMT

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

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

nagaiank — Mon, 13 Jun 2011 23:54:02 GMT

Here is a solution.

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

Tue, 14 Jun 2011 02:10:12 GMT

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

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

nagaiank — Tue, 14 Jun 2011 02:34:18 GMT

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.

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

Tue, 14 Jun 2011 02:44:06 GMT

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

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

Tue, 14 Jun 2011 03:10:10 GMT

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