topic How to rank an expression in QlikView

How to rank an expression

Anonymous — Fri, 23 Jul 2010 15:07:19 GMT

Hi,

I've a straight table with one dimension,and two expressions (one of which is a rank expression) like this:

DIM EXP RANK(EXP)

A 10 1

B 20 2

C 20 2

D 30 4

I need another way to display the RANK(EXP), I mut use this expression as base for others computation and so I need to associate ad the element D the value 3 ( the 3td value in the sequence) and not 4 as in the example.

The result I want looks like this:

DIM EXP RANK(EXP)

A 10 1

B 20 2

C 20 2

D 30 3

Can anyone help me?

Thak you for your help!

How to rank an expression

Fri, 23 Jul 2010 23:30:02 GMT

You can change the way the rank function handles equal ranking using an additional parameter, however, it doesn't appear that any of the options presented will resolve your issue the way you describe.

Technically, B and C are both ranked 2 and 3, but they share the 2 ranking as a result of how QV handles rank.

If you did Rank(Exp, 4) You'd get closest, which would give you:

DIM EXP RANK(EXP)

A 10 1

B 20 2

C 20 3

D 30 4

Depending on your calculation, average rank might also be a better option.

The help files do a reasonably good job of explaining the 'rank' function.

How to rank an expression

Oleg_Troyansky — Sat, 24 Jul 2010 16:38:19 GMT

To Ryan's point, what you request is not quite "Rank", it's a modified version of it. One way to calculate it could be using function above() and comparing the values manually:

Expression1 = Exp, Expression 2= Rank:

if( rowno() = 1, 1,

if( above(Exp) < Exp, above(Rank), Rank))

If the table is very large, this might be heavy...

How to rank an expression

Sun, 25 Jul 2010 07:38:21 GMT

That expression is close, but does not work without minor modification:


if( rowno() = 1, 1,

 if( above([EXP]) < [EXP], above([RANK])+1, above([RANK])))

This accomplishes what you are after.

How to rank an expression

Oleg_Troyansky — Sun, 25 Jul 2010 19:48:23 GMT

Thanks Kevin, that;s exactly what I meant, just managed to confuse myself with my own logic... 🙂

How to rank an expression

Anonymous — Mon, 26 Jul 2010 08:29:34 GMT

Tanks to all for the replies!

I've tried yours expressions..I have a result cose what I want,but there are some errors:

The expression I've use is:

if( [Rank] = 1, 1,
if( above([Exp]) > [Exp], above([Rank])+1, above([Rank])
))

Where I've sobstituded the rowno() function with the [Rank] result because of the table maybe not ordered (I can have moltiple ranking on a table....).

I've defined two main expressions (Exp and Rank) as suggested by Oleg.

Exp-> The main expression (something like sum(....))

Rank-> The base rank expression (something linke Rank(Exp))

And at the end I've defined the "custom" rank expression suggested by Oleg and described above.

The expression faults when after a sequence of elements at the same rank, I have stored the results in a excel file I've attached.

The first column is the dimension, than the Exp, the Rank and the result of the "Costum rank". I've lighted in yellow where the expression faults and I've manually added a column that represent the result I'm looking for.

Thank to all for your help! I hope we can solve this question...there are some days I'm thinking about it...

How to rank an expression

Mon, 26 Jul 2010 12:51:01 GMT

I have attached a QVW here. I have loaded in your spreadsheet as source data and recreated the straight table.

I have two different slight variations of the expression in the table, and they both are giving the correct order...

The expressions used are:

if( rowno() = 1, 1, if( [EXP] = above([EXP]), above([MyRank]), above([MyRank]) + 1 ))

and

if( rowno() = 1, 1, if( above([EXP]) > ([EXP]), above([RANK])+1, above([RANK]) ))

How to rank an expression

Anonymous — Mon, 26 Jul 2010 13:29:17 GMT

Thank you Kevin!

Your expression works well! I've tried something like that this morning...I had got the idea of recursion, but the expression I've wrote fault and so I've tought that qlikview doesn't support recursion in expressions...but your example showes me simply that I make some mistakes writing the expression.

Thank you a lot for your help!

I've another question. The expression you suggest works only on ordered table, do you know a way to modify the expression so that works on not ordered tables?

I ask that because of in my project I've a lot of ranking on the same table. The table header looks like this:

Dimension exp exp1 exp2 exp3 myRank1,myRank2,myRank3

Where myRank1 ranking on exp1, myRank2 on exp2 and so on...

Have you got some idee to solve this problem too?

Thaks!

Matteo

How to rank an expression

Thu, 09 Jun 2011 22:23:41 GMT

What version of QlikView are you using? I am on v9r7 and the 'above' function does not want to work for me.