Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
ali_hijazi
Partner
Partner

how can this be done

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 can walk on water when it freezes
7 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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
Partner
Partner
Author

cool but how?

I can walk on water when it freezes
marcus_sommer
MVP & Luminary
MVP & Luminary

Try it with sort-mode 4 like this:

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

- Marcus

ali_hijazi
Partner
Partner
Author

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 can walk on water when it freezes
marcus_sommer
MVP & Luminary
MVP & Luminary

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
Partner
Partner
Author

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

but the sorting is done on another column!!

I can walk on water when it freezes
marcus_sommer
MVP & Luminary
MVP & Luminary

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