Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Learn how to migrate to Qlik Cloud Analytics™: On-Demand Briefing!
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())))))