Before anyone suggests anything, let me say that I CANNOT access the script.
I'm basically saying something like "count the number of customers that made a payment on an invoice that was due at least 365 days ago". The exact code I used is below. If I use the sum( and put 1 where [Customer Number] is, it will count customers that made multiple payments on 365+ day old invoices multiple times. I am running this on about 1 million customers so I need this to be as fast as possible. I have needed this for other more complicated calculations where the count(distinct made it so that it wouldn't calculate, so this would be very helpful.
= count (distinct /***This is really slow***/ aggr ( if([Invoice Payment Date]>([Invoice Due Date]+365), /***If the payment was made over 1 year after the invoice was due***/ [Customer Number] /***I want the number of customers that made a payment after 1 year after their invoice was due***/ ) ,[Customer Number],[Invoice Number],[Invoice Payment Date] /***You'll just have to trust that I need all three of these***/ ) )
You are correct. I don't see any way to make that more effecient. You need to perform a million if statements, there isn't any way to make that fast. Flags would be an option, but you can't modify the load.