Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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
Honored Contributor II

Re: Count distinct and group by

Hi,

Have you tried like below?

count(Customer)

or

aggr(count(OrderID),Customer,seller)

Capture.JPG

6 Replies
antoniotiman
Honored Contributor III

Re: Count distinct and group by

Hi,

if I have understood, like this

Regards,

Antonio

Not applicable

Re: Count distinct and group by

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.

Re: Count distinct and group by

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

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)
Highlighted
antoniotiman
Honored Contributor III

Re: Count distinct and group by

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
Honored Contributor II

Re: Count distinct and group by

Hi,

Have you tried like below?

count(Customer)

or

aggr(count(OrderID),Customer,seller)

Capture.JPG

Not applicable

Re: Count distinct and group by

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