Count distinct and group by

[#1]

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

[antoniotiman]

Hi,

if I have understood, like this

Regards,

Antonio

[#3]

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.

[Anil_Babu_Samineni]

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

[antoniotiman]

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

[devarasu07 · Accepted solution]

Hi,

Have you tried like below?

count(Customer)

or

aggr(count(OrderID),Customer,seller)

[#7]

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