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