Skip to main content
Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for 
Search instead for 
Did you mean: 
Zaidan_as
Creator
Creator

Wrong result Using Above Function in Set Expression

Introduction first :

[tesIn] is Year-Month name

[In] using Sum(InVal)

[Out] using Sum(OutVal)

[Outstndg] is using this formula = [In]-[Out]+[OB], and right now i'm using this expression :
= (if(len(trim(above(Sum(InVal) - Sum(OutVal))))=0,Sum(InVal) - Sum(OutVal),Sum(InVal) - Sum(OutVal)+above(Sum(InVal) - Sum(OutVal))))

[OB] is the above value of [Outstndg], right now I'm using this expression :
= above(if(len(trim(above(Sum(InVal) - Sum(OutVal))))=0, Sum(InVal) - Sum(OutVal), Sum(InVal) - Sum(OutVal)+above(Sum(InVal) - Sum(OutVal))))

The result is like this

Zaidan_as_0-1648435970303.png

if you see the red colored box, the result is correct. But if you see the blue colored box, the result is wrong, the right value for the Outstndg is 109-90+17 = 36 


This is the raw data

Zaidan_as_1-1648436596644.png

Is there any solution ?

1 Solution

Accepted Solutions
Zaidan_as
Creator
Creator
Author

Ok i already found the answer, 

Right now im using this expression

Outstndg : 
if(len(trim(above(Sum(InVal) - Sum(OutVal))))=0,Sum(InVal) - Sum(OutVal),Sum(InVal) - Sum(OutVal)+RangeSum(above(Sum(InVal)-Sum(OutVal),1,(RowNo()))))

OB :
above(if(len(trim(above(Sum(InVal) - Sum(OutVal))))=0,Sum(InVal) - Sum(OutVal),Sum(InVal) - Sum(OutVal)+RangeSum(above(Sum(InVal)-Sum(OutVal),1,(RowNo())))))

View solution in original post

1 Reply
Zaidan_as
Creator
Creator
Author

Ok i already found the answer, 

Right now im using this expression

Outstndg : 
if(len(trim(above(Sum(InVal) - Sum(OutVal))))=0,Sum(InVal) - Sum(OutVal),Sum(InVal) - Sum(OutVal)+RangeSum(above(Sum(InVal)-Sum(OutVal),1,(RowNo()))))

OB :
above(if(len(trim(above(Sum(InVal) - Sum(OutVal))))=0,Sum(InVal) - Sum(OutVal),Sum(InVal) - Sum(OutVal)+RangeSum(above(Sum(InVal)-Sum(OutVal),1,(RowNo())))))