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

# Keep full table rank value even after filter is applied

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

• ###### Re: Keep full table rank value even after filter is applied

Something like this could work:

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

• ###### Re: Keep full table rank value even after filter is applied

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

• ###### Re: Keep full table rank value even after filter is applied

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.

• ###### Re: Keep full table rank value even after filter is applied

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

• ###### Re: Keep full table rank value even after filter is applied

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

• ###### Re: Keep full table rank value even after filter is applied

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?

• ###### Re: Keep full table rank value even after filter is applied

Try column(1).

Regards

KC

• ###### Re: Keep full table rank value even after filter is applied

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

Regards

KC

• ###### Re: Keep full table rank value even after filter is applied

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

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

still nothing returned...

• ###### Re: Keep full table rank value even after filter is applied

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