Qlik Community

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
elijahabel
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
andrey_krylov
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)

View solution in original post

2 Replies
andrey_krylov
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)

View solution in original post

elijahabel
New Contributor III

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

Thank you, this solution was perfect