RangeSum Above using in Expression

[adamjiawen]

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!!!                 

[anbu1984]

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 ];

[#3]

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.

[preminqlik]

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 ];

[Anonymous]

Hi Adam,

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

Thanks

SHAIK