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

[wizardo]

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

[tresesco · Accepted solution]

Like this?

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

[Sergey_Shuklin]

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]

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