10 Replies Latest reply: Oct 2, 2014 10:37 AM by Ajay Kumar RSS

    Keep full table rank value even after filter is applied

      Hi,

      I have a straight table with 72 rows.  Here's part of them.

       

      TerritoryChange% ChangeRegion Rank
      1201101 - BOSTON, MA20.7%29
      1201102 - PROVIDENCE, RI2912.7%6
      1201103 - HARTFORD, CT-15-5.4%49
      1201104 - ROCHESTER, NY10.6%33
      1201105 - ALBANY, NY229.6%11
      1201106 - NEW HAVEN, CT84.8%23
      1201107 - BRONX, NY112.7%19
      1201108 - LONG ISLAND, NY123.6%18
      1201109 - QUEENS, NY-13-3.7%46
      1201110 - MANHATTAN, NY-58-23.4%72
      1201111 - BROOKLYN, NY178.2%13
      1202101 - NEWARK, NJ20.9%31
      1202102 - EDISON, NJ349.3%4
      1202103 - TRENTON, NJ102.0%21
      1202104 - WILKES-BARRE, PA127.6%16
      1202105 - PHILADELPHIA EAST43.8%26

       

      I am using the following expression to calculate the rank in Region Rank based on change:

       

      rank(total (Change),Territory)

       

      Works fine.

       

      But when I filter out the Territories that start with 1202xxx (because they are in a different region) the region rank recalculates based on only the remaining 1201xxx rows.  This makes sense.

       

      But I need it to display the original region rank based on the 72 rows regardless of what filter is applied.  May anyone shed light on this?

       

      Thanks, JV