Optimize Aggr() Calculation

[Nicole-Smith]

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!

[Gysbert_Wassenaar · Accepted solution]

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.

[Nicole-Smith]

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).

[Gysbert_Wassenaar]

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.

[Christian_Lauritzen]

‌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]

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)