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:

       

      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