# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Not applicable

## 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

10 Replies
Employee

## 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)))

Not applicable

## 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

Honored Contributor II

## 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

Best Regards,
KC
Not applicable

## 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?

Honored Contributor II

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

Try column(1).

Regards

KC

Best Regards,
KC
Honored Contributor II

## 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

Best Regards,
KC
Not applicable

## 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...

Employee

## 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.

Not applicable

## 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

Community Browser