topic Re: Optimize Aggr() Calculation in QlikView

Optimize Aggr() Calculation

Nicole-Smith — Mon, 14 Jan 2013 13:58:15 GMT

I'm currently writing a calculation that needs to count the number of Payers that have a Balance greater than 0 for a particular month. The Balance is a sum of everything in the field Balance up to that month.

This calculation returns the correct information, but it is much too slow to actually use (the chart takes about 5 minutes to calculate when using this): sum(aggr(if(sum(Balance)>0, 1, 0), Payer, Month))

Does anybody know of a faster calculation that I could replace this with? (I am thinking that the slowness is coming from the if statement inside of the aggr() because if I take the if statement out, it runs much faster, but it doesn't return the correct information.)

Any help is much appreciated! Thanks in advance!

Re: Optimize Aggr() Calculation

Gysbert_Wassenaar — Mon, 14 Jan 2013 14:20:24 GMT

You could add a field in the script:

join load

Payer, Month, if(sum(Balance)>0,1,0) as HasPosBal

resident ...fromsomewhere...

group by Payer,Month;

and use the field HasPosBal in your expression.

Re: Optimize Aggr() Calculation

Nicole-Smith — Mon, 14 Jan 2013 15:53:51 GMT

Is there any way to do it without altering the script? There are millions of rows of data and summing in the script with that many rows takes a very long time (on top of the already outrageous amount of time that it takes to load).

Re: Optimize Aggr() Calculation

Gysbert_Wassenaar — Mon, 14 Jan 2013 17:22:58 GMT

I don't think so. You can either take your performance hit in the script or in your charts. Maybe you can find some tips in this thread.

Re: Optimize Aggr() Calculation

Christian_Lauritzen — Sun, 13 Aug 2017 07:12:33 GMT

‌Nicole,

By coincidence I stumbled upon your several years old post.

I might have a solution for you. Try:

sum(aggr(floor((sign(sum(Balance))+1)/2), Payer, Month))

This is a way around time consuming if statements, and should be much faster. Sign() returns 1 for positive balance, 0 for 0 balance and -1 for negative balances. by adding one and dividing by two, negative lances become 0, zero balances become 0.5 and our desired positive balances become 1. Floor() cuts the decimal, so the zero balances also return 0.

We are left with 1 for each positive balance; The same result without the IF statement.

/Chris

Re: Optimize Aggr() Calculation

qliksus — Tue, 15 Aug 2017 05:03:01 GMT

Maybe you can create a Key field by having Prayer and Month together (Prayer&Month) and use that in set analysis like the below

Sum( {<Key={"=sum(Balance)>0"}>} 1)