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:

 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

1 Solution

Accepted Solutions
Honored Contributor II

Re: Count distinct and group by

Hi,

Have you tried like below?

count(Customer)

or

aggr(count(OrderID),Customer,seller)

6 Replies
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 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 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
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

Honored Contributor II

Re: Count distinct and group by

Hi,

Have you tried like below?

count(Customer)

or

aggr(count(OrderID),Customer,seller)

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