Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

I need something faster than count(distinct

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.

4 Replies
Not applicable
Author

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


Not applicable
Author

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
Partner
Partner

Hi,

you can enter a new field in your Table:

1 as Recordcounter

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

Not applicable
Author

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.