1 Reply Latest reply: Jun 30, 2018 1:29 AM by Olivier GAUTIER RSS

    How to rank with 3 dimension

    Zeshun Cai

      Hi all,

       

      I am trying to use the rank function to figure out how to rank sales with three different dimensions(Type, Office Name and Name). Please see tables below. My issue with the my rank function is that it returns rank of 4 for both De in office D and E, table 1 below.

       

      Here's the function I used, aggr(rank(sales,4,1),type,name), I tried adding the third dimension office name hoping that would work,  aggr(rank(sales,4,1),type,name, Office Name) would return rank of 1s to all the rows. Please let me know if there's way to accomplish this. The correct ranking should be in the 2nd table, you can see that De in office E should rank 6th for both Type A and B. Thanks again for your time!

       

         

      RankTypeOffice NameNameSales
      1AABob800
      2ABsally700
      3ACSussy600
      4ADDe300
      4AEDe200
      5AFTom400
      1BABob800
      2BBsally700
      3BCSussy600
      4BDDe300
      4BEDe200
      5BFTom400

       

         

      RankTypeOffice NameNameSales
      1AABob800
      2ABsally700
      3ACSussy600
      4ADDe300
      5AFTom400
      6AEDe200
      1BABob800
      2BBsally700
      3BCSussy600
      4BDDe300
      5BFTom400
      6BEDe200