Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

wizardo
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?Smiley Happy

sample data:

 

CountryCustomerSale
israel a50
israel b40
israel c50
israel d30
israel e50
israel f20
englandg300
englandh400
englandi250
englandj400
englandk125


and the result i need:

 

CountryMax SaleNo of cust with max sale
israel 503
england4002


thank you

1 Solution

Accepted Solutions
MVP
MVP

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

Like this?

Capture.PNG

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

3 Replies
MVP
MVP

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

Like this?

Capture.PNG

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

serj_shu
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)

wizardo
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



Community Browser