Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
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