topic Re: Ranking issue in QlikView

Ranking issue

triciagdaly — Mon, 05 Jan 2015 15:29:31 GMT

I am trying to rank the PPV with the 10 least and 10 greatest entries for item. I tried using the following in the expression for the EXT Variance Amt / PPV value, and it returned 1 for all rows.

if(rank( -([Ext Variance Amt / PPV]), 0, 1)<=10, Red(),
if(rank( ([Ext Variance Amt / PPV]), 0, 1)<=10, Green()
))

See the attached screen shot also.

Re: Ranking issue

rubenmarin — Mon, 05 Jan 2015 15:34:41 GMT

Hi Tricia, you tried with rank(TOTAL ... )? I can be more helpful with a sample data

Re: Ranking issue

agomes1971 — Mon, 05 Jan 2015 15:39:43 GMT

Hi,

Tricia,

please see my attached file its just an example i hope it helps.

Regards

André Gomes

Re: Ranking issue

triciagdaly — Mon, 05 Jan 2015 19:54:59 GMT

Andre,

Not very helpful as I'm not wanting to use a button to show the ranking. Thanks!

Re: Ranking issue

Mon, 05 Jan 2015 21:23:05 GMT

Attached is a simple example on how to do it using 'Visual Cues' dynamically.

Hope it helps.

Re: Ranking issue

simondachstr — Mon, 05 Jan 2015 21:51:45 GMT

You'll need to do an aggregation on the ranks. Every item will be regarded in isolation on a row basis which will always result to rank=1.

Re: Ranking issue

triciagdaly — Tue, 06 Jan 2015 22:22:59 GMT

I'm having syntax issues using aggr(rank....

The below works with one dimension but I have multiple.

if(rank( -[Ext Variance Amt / PPV], 0, 1)<=10, Red(),
if(rank( [Ext Variance Amt / PPV], 0, 1)<=10, Green()
))

Help with syntax is appreciated.

Re: Ranking issue

triciagdaly — Tue, 06 Jan 2015 22:24:57 GMT

Note: The above expression is defined as text color for the expression 'Ext Variance Amt / PPV' field.

Re: Ranking issue

Tue, 06 Jan 2015 22:40:56 GMT

Add mode 4 instead of 1. 0,4 instead of 0,1

Re: Ranking issue

Tue, 06 Jan 2015 22:43:09 GMT

or use

if(rank(TOTAL -[Ext Variance Amt / PPV], 0, 1)<=10, Red(),
if(rank(TOTAL [Ext Variance Amt / PPV], 0, 1)<=10, Green()
))

Re: Ranking issue

simondachstr — Wed, 07 Jan 2015 09:51:44 GMT

Try

=if(max(aggr(rank(rank( -[Ext Variance Amt / PPV], 0, 1), Item)))<=10, Red(),

if(max(aggr(rank(rank( -[Ext Variance Amt / PPV], 0, 1), Item)))<=10, Green()))

Or even better - upload a sample app with scrambled data.

Re: Ranking issue

triciagdaly — Wed, 07 Jan 2015 14:36:56 GMT

Thank you all so much for your help. Using the Total worked !