Top 5 in google maps

[#1]

Hi Guys,

I'm struggling to get the top 5 competitors per region in google maps.

I have 4 fields:

SalesPerson

Competitor

Business Unit

ZIP code

I need to show the top 5 of each Region. Each SalesPerson repesents a region and a business unit.

So SalesPerson John represents: ZIP 10 till 50 and is from the business unit: A

I have ZIP code as an dimension and tried the following three expressions:

if(rank(count(if(SalesPerson='John', Competitor)),4)<=5, Count(Competitor),0)

Aggr(rank(count(Competitor),4)<=5, SalesPerson)

if(rank(count(Competitor),4)<=5, count(Competitor),0)

The problem with the first expression is that it only works if I select John, the other problem is that I need to make an expression for each salesperson, which is not efficient. The second expression doesn't work. The third expression takes the top 5 of all the regions in total, which is not what i want.

Last but not least, I need to display who these competitors are, So I tried the following:

=Competitors & ': ' & Aggr(rank(count(Competitor),4)<=5, SalesPerson)

=Competitors & ': ' & if(rank(count(if(SalesPerson='John', Competitor)),4)<=5, Count(Competitor),0)

Hope some one can help or point me to the right direction.

Carter

[#2]

Any one?

I justed tried another expression:

=aggr(if(rank(count(Competitor),4)<=5, count(Competitor),0),SalesPerson, BusinessUnit)

But that didn't work

[luciancotea]

Top 5 competitors....Top of sales I suppose.

Here's an expression to get Top 5 Customers Sales of each month in a Chart with YearMonth and Customer as dimensions.. You can ajust it to work for you:

= if( aggr(rank(  SUM(Sale_Value)), YearMonth, Customer ) <= 5,

      Sum(Sale_Value)

    )