How to create a persistent rank in a pivot

[#1]

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:

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:

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

Many thanks

Howard

[chrislofthouse]

Hi,

Can you provide a copy?

Cheers

[#3]

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

Thanks

[JonnyPoole]

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.

!

[Oleg_Troyansky]

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!

[#6]

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.

[#7]

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:

[#8 · Accepted solution]

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