Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to create a persistent rank in a pivot

Hi,

I am developing a stock sorting and filtering application in QlikSense.  I have a statistic called RS to which I want to apply a rank. The rank needs to persist when I apply an industry selection to my stocks.

Here is a screenshot of my app:

Screen1.JPG

I have written a measure to achieve this:

RankRSGlobal = rank(sum({1<Industry= >}RS),1,1)

which works as required and displays the persistent rank however the stocks are not filtered when the industry is selected - I see all stocks and the value where the Stock matches the selected Industry:

Screen2.JPG

Any tips on how to achieve a filtered stock list with the persistent rank?

Many thanks

Howard

1 Solution

Accepted Solutions
Not applicable
Author

OK - thanks to Oleg.  I have solved this.  The resolution was to rewrite the measure as follows:

=avg(aggr(rank( sum( {$<Industry=>} RS),1,1 ), Period,Ticker))

View solution in original post

7 Replies
chrislofthouse
Partner Ambassador
Partner Ambassador

Hi,

Can you provide a copy?

Cheers

Not applicable
Author

Have added the file to the original post....

Thanks

JonnyPoole
Employee
Employee

I replaced your dimension from an expression of:  Ticker

To:  =aggr( if (  Avg(RS) = null() ,null(),Ticker) , Ticker)

Then uncheck 'show nulls' under the dimension.

Capture.PNG !

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Check out various ranking solution in this blog article:

Q-Tip #8 – Ranking the Unrankable | Natural Synergies

It's written for QlikView, but most of the concepts should apply to Qlik Sense as well.

cheers,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

Not applicable
Author

Thanks Jonathan but this solution changes the values of both of the rank columns - the required result is that the value in the RankRSGlobal column is unchanged from the unfiltered view so for example AAL would always show 413.

Not applicable
Author

Thanks Oleg that post was very helpful and articulates exactly the issue that I am facing.  I have re-written the measure in line with the articles suggestion as follows:

=avg(aggr(rank( sum( {$<Industry=>} RS) ), Industry))

However this gives both incorrect results and all but the most recent periods are blank.... see screenshot:Capture.JPG

Not applicable
Author

OK - thanks to Oleg.  I have solved this.  The resolution was to rewrite the measure as follows:

=avg(aggr(rank( sum( {$<Industry=>} RS),1,1 ), Period,Ticker))