Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
got a pivot table with one dimension
I want to highlight the first x rows of (x is specified by user)
suppose x = 6
then if the first 5 lines got the following values:
55
116
21
21
21
20
then I need to give a ranking number for each rows which will be as follows:
55 2
116 1
21 3
21 3
21 3
20 4
how can I make 20 be ranked as 4 ?
I used the rank function and it gives 6 instead
please advise
I believe this won't be possible directly with qv rank-functions. But I could imagine if you used one or two help-expressions which check the rank above() and check the max-value until the row maybe combined with some kind of sorting ... really nice is other.
- Marcus
cool but how?
Try it with sort-mode 4 like this:
num(rank(sum(value), 4))
- Marcus
NO
I got 278 rows with Value 100% i.e. the above rank function gives 1-278
the rank of the row whose value 97.6% got 279 as rank whereas I want it to have it 2 as rank
I think you will need some adjustments on the sort-mode and checks inside the if-loop but something from this main-logic could work:
if(sum(value) = above(sum(value)),
above(num(rank(sum(value), 4))) + 1,
num(rank(sum(value), 4)))
- Marcus
this works if you want to rank the column that you are sorting on
but the sorting is done on another column!!
Then the checks would be more complicated and you have to check the max. value above and below from your value:
rangemax(above(sum(value), 0, rowno()))
rangemax(below(sum(value), rowno() + 1, noofrows()))
- Marcus