Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Optimize Aggr() Calculation

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!

1 Solution

Accepted Solutions

Re: Optimize Aggr() Calculation

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.


talk is cheap, supply exceeds demand
5 Replies

Re: Optimize Aggr() Calculation

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.


talk is cheap, supply exceeds demand

Re: Optimize Aggr() Calculation

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

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.


talk is cheap, supply exceeds demand
christianlaurit
Contributor II

Re: Optimize Aggr() Calculation

‌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

qliksus
Valued Contributor

Re: Optimize Aggr() Calculation

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)

Community Browser