Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
elijahabel
Contributor III
Contributor III

Any way to substitute zero in the case an aggregation is <0

  I have this issue where we have a complex aggregate calculation (High water mark), and we need to return 0 if the result of the aggregation is less than 0. Obviously i could throw it into an if statement, but i think performance-wise this is not a good idea.

Calc:

Max(Aggr(RangeSum(Above(Sum(Value), 0, RowNo())), Department,(Date, (TEXT, Ascending)))))

If statement solution:

if(Max(Aggr(RangeSum(Above(Sum(Value), 0, RowNo())), Department,(Date, (TEXT, Ascending))))) < 0, 0, Max(Aggr(RangeSum(Above(Sum(Value), 0, RowNo())), Department,(Date, (TEXT, Ascending))))))


Is there a cleaner way to accomplish this?

1 Solution

Accepted Solutions
andrey_krylov
Specialist
Specialist

HI Elijah. Maybe RangeMax

RangeMax(Max(Aggr(RangeSum(Above(Sum(Value), 0, RowNo())), Department,(Date, (TEXT, Ascending))))), 0)

View solution in original post

2 Replies
andrey_krylov
Specialist
Specialist

HI Elijah. Maybe RangeMax

RangeMax(Max(Aggr(RangeSum(Above(Sum(Value), 0, RowNo())), Department,(Date, (TEXT, Ascending))))), 0)

elijahabel
Contributor III
Contributor III
Author

Thank you, this solution was perfect