This content has been marked as final. Show 2 replies
I'm struggling to get the top 5 competitors per region in google maps.
I have 4 fields:
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)
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.