Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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.

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

10 Replies
Employee
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)))

Capture.PNG.png

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


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

jyothish8807
Honored Contributor II

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

Try column(1).

Regards

KC

Best Regards,
KC
jyothish8807
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
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