how can this be done

[ali_hijazi]

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

[marcus_sommer]

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

[ali_hijazi]

cool but how?

[marcus_sommer]

Try it with sort-mode 4 like this:

num(rank(sum(value), 4))

- Marcus

[ali_hijazi]

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

[marcus_sommer]

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

[ali_hijazi]

this works if you want to rank the column that you are sorting on

but the sorting is done on another column!!

[marcus_sommer]

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