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

[JonnyPoole]

Something like this could work:

if( IsNull(Change) <> -1, rank( TOTAL only({1}Change)))

[#3]

Jonathan,

Nice, but doesn't quite work. Your solution has change as a dimension.  In my table, Change is a calculated field based on an expression.  When I paste in the expression

if( IsNull(Change) <> -1, rank( TOTAL only({1}Change)))

I get the   Bad field names(s): Change  complaint.

Any way to tell QV to treat the Change field as a Dimension?  Or another idea?

Thanks, JV

[jyothish8807]

Hi,

Try this attach.

If it gives an error for change field,then replace change with column(n).

n--- the column no of change field in your chart.(expression field).

Regards

KC

[#5]

tried both:

if( IsNull(Change)<>-1,rank(TOTAL only ({1} column(2))))

if( IsNull(column(2))<>-1,rank(TOTAL only ({1} column(2))))

and both said Expression OK

But didn't work.  No rank returned.  Is syntax wrong?

[jyothish8807]

Try column(1).

Regards

KC

[jyothish8807]

QV takes first expression field as column(1) and so on.So according to that cll the column number.

Regards

KC

[#8]

if( IsNull(column(1))<>-1,rank(TOTAL only ({1} column(1))))

if( IsNull(Change)<>-1,rank(TOTAL only ({1} column(1))))

still nothing returned...

[JonnyPoole]

I would need your QVW to review. i assumed the data in the first post was raw data, and my sample was based off that,  but that is not the case.

[#10]

Hi Jonathan,

Thanks for the effort.  This a massive, convoluted app that can't easily be dissected to provide a .qvw sample.  I'll try some other things, and may come back to this later.

JV