topic Keep full table rank value even after filter is applied in QlikView

Keep full table rank value even after filter is applied

Thu, 02 Oct 2014 02:06:37 GMT

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

JonnyPoole — Thu, 02 Oct 2014 03:39:19 GMT

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

Thu, 02 Oct 2014 04:02:02 GMT

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

jyothish8807 — Thu, 02 Oct 2014 04:37:46 GMT

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

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

Thu, 02 Oct 2014 04:56:51 GMT

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

jyothish8807 — Thu, 02 Oct 2014 05:17:27 GMT

Try column(1).

Regards

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

jyothish8807 — Thu, 02 Oct 2014 05:19:19 GMT

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

Regards

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

Thu, 02 Oct 2014 13:45:53 GMT

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

JonnyPoole — Thu, 02 Oct 2014 14:11:14 GMT

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

Thu, 02 Oct 2014 14:37:14 GMT

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.

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

Anonymous — Thu, 02 Oct 2014 14:37:42 GMT

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