Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count distinct and group by

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:



 

sellerOrderIDCustomer
seller11Customer1
seller21Customer2
seller31Customer3
seller41Customer4
seller51Customer5
seller12Customer1
seller22Customer2
seller32Customer3
seller42Customer4
seller52Customer5
seller13Customer1
seller23Customer2
seller33Customer3
seller43Customer4
seller53Customer5


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


Thanks in advance for help

1 Solution

Accepted Solutions
devarasu07
Master II
Master II

Hi,

Have you tried like below?

count(Customer)

or

aggr(count(OrderID),Customer,seller)

Capture.JPG

View solution in original post

6 Replies
antoniotiman
Master III
Master III

Hi,

if I have understood, like this

Regards,

Antonio

Not applicable
Author

Hi Antonio,

Thanks for reply ,

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

  

Étiquettes de lignesNombre distinct de idorder
seller13
seller23
seller33
seller43
seller53
Total général3

but the right total results should be like that: 15

  

Étiquettes de lignesNombre distinct de idorder
seller13
seller23
seller33
seller43
seller53
Total général15

Thanks for help.

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
antoniotiman
Master III
Master III

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
Master II
Master II

Hi,

Have you tried like below?

count(Customer)

or

aggr(count(OrderID),Customer,seller)

Capture.JPG

Not applicable
Author

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