4 Replies Latest reply: May 6, 2014 8:31 AM by khadar basha

RangeSum Above using in Expression

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?

• Re: RangeSum Above using in Expression

In Script

Key,Column1,  Column2

1,2,      4

2, 16,     8

3, 19,    12

4, 22,    16

5,5,     20 ];

• Re: RangeSum Above using in Expression

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

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

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.