5 Replies Latest reply on Feb 2, 2017 1:37 AM by Avia Mak

    Rank across complete data set

    Avia Mak

      Hi All,

       

      Need assistance in terms to develop a pivot table with Rank function:

       

      Requirement:

       

      We have a map where we are plotting Rank City wise based on Score.

      If a particular user click on a specific city, a pivot table will be shown with the rank of the selected city in comparision to the rest of cities

       

      Data is as follows:

       

         

      YearCityLevel1Level2Level3Level4Score
      2015ABCEnbPE & SEPEQ184.62
      2015ABCEnbPE & SEPEQ290.35
      2015ABCEnbPE & SEPEQ380.00
      2015ABCEnbPE & SEEGQ458.82
      2015ABCEnbPE & SEEGQ561.21
      2015ABCEnbPE & SEEGQ683.07
      2015PQREnbPE & SEPEQ134.62
      2015PQREnbPE & SEPEQ235.74
      2015PQREnbPE & SEPEQ390.00
      2015PQREnbPE & SEEGQ460.00
      2015PQREnbPE & SEEGQ580.00
      2015PQREnbPE & SEEGQ665.00

       

       

       

      Complete Pivot table output:

         

      CityLevel1Level2Level3Level4ScoreRank
      ABCEnb76.341
      PE & SE76.341
      PE84.991
      Q184.621
      Q290.351
      Q380.002
      EG67.702
      Q458.822
      Q561.212
      Q683.072
      PQREnb60.891
      PE & SE60.892
      PE53.452
      Q134.622
      Q235.742
      Q390.001
      EG68.331
      Q460.001
      Q580.001
      Q665.001

       

       

      Final pivot table would show only 1 city at a time i.e based upon the user selection in the map Eg.if City 'ABC' selected follwoing would be the output:

       

         

      CityLevel1Level2Level3Level4ScoreRank
      ABCEnb76.341
      PE & SE76.341
      PE84.991
      Q184.621
      Q290.351
      Q380.002
      EG67.702
      Q458.822
      Q561.212
      Q683.072

       

       

      I hope I was clear in explaining my query.

       

      Thanks in advance