Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save \$400. Learn More
Highlighted
New 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?

Tags (2)
1 Solution

Accepted Solutions
Valued Contributor

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

HI Elijah. Maybe RangeMax

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

2 Replies
Valued Contributor

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

HI Elijah. Maybe RangeMax

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

New Contributor III

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

Thank you, this solution was perfect