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