Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Any one?
I justed tried another expression:
=aggr(if(rank(count(Competitor),4)<=5, count(Competitor),0),SalesPerson, BusinessUnit)
But that didn't work
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)
)