Keep full table rank value even after filter is applied

[#1]

Hi,

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

Territory	Change	% Change	Region Rank
1201101 - BOSTON, MA	2	0.7%	29
1201102 - PROVIDENCE, RI	29	12.7%	6
1201103 - HARTFORD, CT	-15	-5.4%	49
1201104 - ROCHESTER, NY	1	0.6%	33
1201105 - ALBANY, NY	22	9.6%	11
1201106 - NEW HAVEN, CT	8	4.8%	23
1201107 - BRONX, NY	11	2.7%	19
1201108 - LONG ISLAND, NY	12	3.6%	18
1201109 - QUEENS, NY	-13	-3.7%	46
1201110 - MANHATTAN, NY	-58	-23.4%	72
1201111 - BROOKLYN, NY	17	8.2%	13
1202101 - NEWARK, NJ	2	0.9%	31
1202102 - EDISON, NJ	34	9.3%	4
1202103 - TRENTON, NJ	10	2.0%	21
1202104 - WILKES-BARRE, PA	12	7.6%	16
1202105 - PHILADELPHIA EAST	4	3.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

[Anonymous]

Hi ,

If i understand your query correctly : you would like to display the RANK - irrespective of the Field value selection in Territory;

In that case below query should be helpful.

rank( total   ONLY( {1}  Change), Territory)

Regards,

Ajay Kumar