I need something faster than count(distinct

[#1]

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***/
)
)

Appreciate it.

[#2]

First, If statements are notoriously slow. Use Set Analysis:

=count({<[Invoice Payment Date]={'>$(=Date([Invoice Due Date]+365))'}>}
distinct aggr([Customer Number]
 ,[Customer Number],[Invoice Number],[Invoice Payment Date]
 )
)

[#3]

Sets are only calculated once per table and since the invoice due date changes for every customer and every invoice, this won't work. Correct me if I'm wrong.

[brenner_martina]

Hi,

you can enter a new field in your Table:

1 as Recordcounter

Then you can use Sum(Recordcounter ....)

[#5]

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.