topic Re: Aggregate Count Function in QlikView

Aggregate Count Function

Anonymous — Sun, 22 Apr 2018 16:26:33 GMT

Hello,

Please see the issue I'm facing and the desired result that I'm trying to achieve. The desired result needs to be a Straight Table chart. Could someone please help me get the desired result?

Here is my database table:

AccountNumber	OrderNumber	Complete
A0001	100.001	Y
A0001	100.002	Y
A0001	100.003	N
A0002	100.005	Y
A0002	100.006	N
A0003	100.007	N

Desired result in a Straight table Chart:
NB: Complete% calculation is total Complete 'Y' by AccountNumber divide by Total OrderNumber by AccountNumber.

AccountNumber	Complete%
A0001	66.67%
A0002	50%
A0003	0%

Currently, I am getting the total Complete% of the overall OrderNumbers instead of OrderNumbers by AccountNumber.

AccountNumber	Complete%
A0001	33.33%
A0002	16.67%
A0003	0%

Thank you!

Re: Aggregate Count Function

Oleg_Troyansky — Sun, 22 Apr 2018 17:18:03 GMT

You are probably using the keyword TOTAL, which is not necessary here.

Something along these lines should work:

count({<Complete={'Y'}>} distinct OrderNumber)/

count(distinct OrderNumber)

You can learn more about Set Analysis and advanced aggregation in my book QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense

Cheers,

OlegTroyansky

Re: Aggregate Count Function

Anonymous — Mon, 23 Apr 2018 01:49:44 GMT

‌I think I did use the word “Total”. I will check back the application and update more information tomorrow. Thank you!