Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Re: How to create a persistent rank in a pivot

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))

7 Replies
chrislofthouse
Contributor

Re: How to create a persistent rank in a pivot

Hi,

Can you provide a copy?

Cheers

Not applicable

Re: How to create a persistent rank in a pivot

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

Thanks

Employee
Employee

Re: How to create a persistent rank in a pivot

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 !

MVP
MVP

Re: How to create a persistent rank in a pivot

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

Re: How to create a persistent rank in a pivot

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

Re: How to create a persistent rank in a pivot

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

Re: How to create a persistent rank in a pivot

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))