Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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