topic Re: Count distinct and group by in QlikView

Count distinct and group by

Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

I'm still new in Qlikview and I need your help to know how to count occurences within using sql. I wanna to count the number of orderes per seller KPI ,but i couldn't get correct results because on data table source many sellers had the same IdOder :

Here is how my simplified the table:

seller	OrderID	Customer
seller1	1	Customer1
seller2	1	Customer2
seller3	1	Customer3
seller4	1	Customer4
seller5	1	Customer5
seller1	2	Customer1
seller2	2	Customer2
seller3	2	Customer3
seller4	2	Customer4
seller5	2	Customer5
seller1	3	Customer1
seller2	3	Customer2
seller3	3	Customer3
seller4	3	Customer4
seller5	3	Customer5

i used aggr(count (distinct IdOrder), Seller) but doesn't work

Thanks in advance for help

Re: Count distinct and group by

antoniotiman — Tue, 27 Jun 2017 22:27:37 GMT

Hi,

if I have understood, like this

Regards,

Antonio

Re: Count distinct and group by

Thu, 29 Jun 2017 18:03:50 GMT

Hi Antonio,

Thanks for reply ,

When i do count distinct i get as total results 3 because sellers had the same IdOrder

Étiquettes de lignes	Nombre distinct de idorder
seller1	3
seller2	3
seller3	3
seller4	3
seller5	3
Total général	3

but the right total results should be like that: 15

Étiquettes de lignes	Nombre distinct de idorder
seller1	3
seller2	3
seller3	3
seller4	3
seller5	3
Total général	15

Thanks for help.

Re: Count distinct and group by

Anil_Babu_Samineni — Fri, 30 Jun 2017 04:00:01 GMT

Can you try default option like Total Mode in Sum, May be?

Re: Count distinct and group by

antoniotiman — Fri, 30 Jun 2017 04:14:16 GMT

Hi,

If You have Straight Table set Total mode to Sum of Rows (as Anil suggests)

or use this expression

Sum(Aggr(Count(DISTINCT OrderID),seller))

Regards,

Antonio

Re: Count distinct and group by

devarasu07 — Fri, 30 Jun 2017 04:16:49 GMT

Hi,

Have you tried like below?

count(Customer)

aggr(count(OrderID),Customer,seller)

Re: Count distinct and group by

Fri, 30 Jun 2017 16:38:33 GMT

Big Thanks to all of you for your helps :

As you suggested ,i tried this below expression Sum(Aggr(Count(DISTINCT OrderID),seller)) and i set table to sum mode on rowns and it works now.

regards,

Imane