topic Re: how can this be done in QlikView

how can this be done

ali_hijazi — Mon, 02 Feb 2015 12:23:48 GMT

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:

116

then I need to give a ranking number for each rows which will be as follows:

55 2

116 1

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

Re: how can this be done

marcus_sommer — Mon, 02 Feb 2015 13:14:49 GMT

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

Re: how can this be done

ali_hijazi — Mon, 02 Feb 2015 13:29:40 GMT

cool but how?

Re: how can this be done

marcus_sommer — Mon, 02 Feb 2015 13:48:30 GMT

Try it with sort-mode 4 like this:

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

- Marcus

Re: how can this be done

ali_hijazi — Mon, 02 Feb 2015 14:14:24 GMT

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

Re: how can this be done

marcus_sommer — Mon, 02 Feb 2015 14:31:07 GMT

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

Re: how can this be done

ali_hijazi — Mon, 02 Feb 2015 14:43:13 GMT

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

but the sorting is done on another column!!

Re: how can this be done

marcus_sommer — Mon, 02 Feb 2015 14:57:56 GMT

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