Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Have you tried like below?
count(Customer)
or
aggr(count(OrderID),Customer,seller)
Hi,
if I have understood, like this
Regards,
Antonio
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.
Can you try default option like Total Mode in Sum, May be?
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
Hi,
Have you tried like below?
count(Customer)
or
aggr(count(OrderID),Customer,seller)
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