topic Re: RangeSum Above using in Expression in QlikView

RangeSum Above using in Expression

adamjiawen — Tue, 06 May 2014 08:29:53 GMT

Hello everybody:

I've met a problem,using rangesum and above

Problem:

Key Column1 Column2 Column3

1 2 4 0

2 16 8 8

3 19 12 15

4 22 16 21

5 5 20 21

logical:

Column2 has been accounted ,it grows 4 than row before

what I need to account is Column3

when (Column1-Column2) (ROW1) <0 Column3 is 0

when (Column1-Column2)(ROW2~4)>0 Column3=Column1-Column2+Column3(row before)

for example :

row2 Column3=16-8+0=8

row3 Column3=19-12+8=15

row5 when (Column1-Column2)<0(5-20<0) Column3=0+21=21

how can I use the expression to achieve the logical in vetical table?

thanks for your help!!!

Re: RangeSum Above using in Expression

anbu1984 — Tue, 06 May 2014 08:44:30 GMT

In Script

Load *, If(Column1-Column2<0,If(IsNull(Peek(Column3)),0,Peek(Column3)),Column1-Column2+If(IsNull(Peek(Column3)),0,Peek(Column3))) As Column3;

Load * Inline [

Key,Column1, Column2

1,2, 4

2, 16, 8

3, 19, 12

4, 22, 16

5,5, 20 ];

Re: RangeSum Above using in Expression

Tue, 06 May 2014 08:58:52 GMT

Hi,

Should work in a pivot chart:

if([Column1]-[Column2]>0, [Column1]-[Column2]+Above([Column3]), 0)

I assumed case when Column1=Column2 goes with [Column1]-[Column2]<0

Hope it helps.

Re: RangeSum Above using in Expression

preminqlik — Tue, 06 May 2014 09:28:50 GMT

hi anbu , your code is correct , please add rangesum(column,0) because it fills null values with 0 , since if any column measure have null or value column have null then rangesum will take care of that see the below code

Load *, rangesum(If(CColumn1-CColumn2<0,If(IsNull(Peek(Column3)),0,Peek(Column3)),CColumn1-CColumn2+If(IsNull(Peek(Column3)),0,Peek(Column3))),0) As Column3;

Load *,rangesum(Column1,0) as CColumn1,

rangesum(Column2,0) as CColumn2 Inline [

Key,Column1, Column2

1,2, 4

2, 16, 8

3, 19,

4, 22, 16

5,5, 20 ];

Re: RangeSum Above using in Expression

Anonymous — Tue, 06 May 2014 12:31:54 GMT

Hi Adam,

please find the attachment of test application,please let me know for any clarification.

Thanks

SHAIK