2 Replies Latest reply: Feb 20, 2012 11:17 AM by Lucian Cotea RSS

    Top 5 in google maps

      Hi Guys,


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

      I have 4 fields:




      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.