topic recursive sum in QlikView

recursive sum

Tue, 20 Dec 2011 12:19:50 GMT

ThanksHello to everybody

I need to have a calculate columns starting from 2 columns

A	B	C	n
0	100	100	1
20	0	120	2
30	0	150	3
10	0	160	4
10	0	170	5

if A=0

C=B

if A <> 0 C=A+C(n-1)

I have tried an Expression like this

if(A=0, B,rangesum( A,above(C,0,RowNo()) ) )

but it doesn't works.

Does anyone have any suggestions for me?

Thanks

Oronzo

recursive sum

Sokkorn — Tue, 20 Dec 2011 12:34:11 GMT

Hi Oronzo,

Your expression

if A <> 0 C=A+C(n-1)

Can use this if A <> 0 then C=A/(2-n) ?

If like this it easy to calculate value.

Regards,

Sokkorn Cheav

recursive sum

Tue, 20 Dec 2011 13:36:16 GMT

My goal is to have the sum of A with the previous value of C ( C(n-1)..)

C=A+C(n-1)

Thanks

recursive sum

swuehl — Tue, 20 Dec 2011 13:48:04 GMT

Not sure if this fulfills all your requirements, but with the given sample, this results in the expected values for C:

=aggr( rangesum(above( A,0,RowNo()))+ rangesum(above( B,0,RowNo())),n)

Re: recursive sum

Tue, 20 Dec 2011 14:27:37 GMT

Hello

Thank you for your answer, but is not exactly what I would to obtain

In attachement I have put an example

With this expression

if(A=0,B,aggr( rangesum(above( A,0,RowNo()))+ rangesum(above( B,0,RowNo())),n))

regard

Re: recursive sum

swuehl — Tue, 20 Dec 2011 14:31:57 GMT

Yes, I already thought you would like to reset the overall value on zero values in field A, i.e. you have multiple segements that you want to sum up separately, right?

Can this be done in the script? Would be much easier then, or do you need to be sensitive to selections?

Re: recursive sum

Tue, 20 Dec 2011 14:38:00 GMT

Thank you for your answer,

But since I use QlikView just need to know how to load the script.

Many Thanks

Oronzo

Re: recursive sum

swuehl — Tue, 20 Dec 2011 14:43:13 GMT

In the script, I would do it like this:

Result:

LOAD n, A, B,

if(A=0, B, A+ peek(C)) as C

resident YourTable order by n;

drop table YourTable;

where YourTable is the Input table already loaded with fields n,A,B.

Regards,

Stefan

Re: recursive sum

Thu, 22 Dec 2011 09:34:36 GMT

Thanks for your answer, I must analyze well my data

Regards

Oronzo