# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Contributor III

## count customers whos sales per country, matches the max sale per country

Hi all,

my data is more complex but for the sake of this question lets assume:

simple table

country, customer and sales

now i have a pivot with country as dimension

and max(sales) as expression

that works and gives me the highest sales in each country

now it happens so in each country there are more then 1 customer with the highest sale

i want to count those

any ideas on how?

sample data:

 Country Customer Sale israel a 50 israel b 40 israel c 50 israel d 30 israel e 50 israel f 20 england g 300 england h 400 england i 250 england j 400 england k 125

and the result i need:

 Country Max Sale No of cust with max sale israel 50 3 england 400 2

thank you

1 Solution

Accepted Solutions
MVP

## Re: count customers whos sales per country, matches the max sale per country

Like this?

FirstSortedValue( Aggr(Count(Customer),Sale, Country), -Aggr(Sale, Sale, Country))

3 Replies
MVP

## Re: count customers whos sales per country, matches the max sale per country

Like this?

FirstSortedValue( Aggr(Count(Customer),Sale, Country), -Aggr(Sale, Sale, Country))

Valued Contributor

## Re: count customers whos sales per country, matches the max sale per country

Hello, Daniel!

Or you can achieve this using a temporary variable:

=sum(if(sum(aggr(sum(Sale),Country,Customer))=\$(#=max(aggr(sum(Sale),Country))),1,0)

Contributor III

## Re: count customers whos sales per country, matches the max sale per country

your answer is the  "correct".

###### serj_shu

his answer is not the right one, it was how i approached the problem too but it doesnt work.

your answer tho is genius, it took me while to understand how come the firstsortedvalue function works here

but once i realised how, it hit me how simply great is your approach

what was baffling to me was that "FirstSortedValue" returens a null if the highest number appears in more then 1 row.

but since your using it on an aggregation, that insures there will never be to values the same the top.

great.

thank you

Daniel