Ranking values with time selection

[thomasmercer]

I'm making a dashboard where a user can select a fund,  and information on how that fund is 'ranked' compared to other funds will appear. I also want the user to be able to select a time period, which will recalculate the rank on the fly.

I have several measures which I am trying to 'convert' into rank functions, but can't figure out how to do so in a way that both considers the ranking based on the pool of clients and recalculates based on the user filtered time period.

For example a working master measure I currently have for automation rate is:

sum({<[Failure Code-reason]={"Success"}>}jobs)/Sum(jobs)

That is, how many jobs have [Failure Code-reason] = "Success" out of the total pool. I want to convert this to a rank based on [Fund Custom Name] . I've tried several things and the closest I've got is:

Aggr({1} Rank(sum({<[Failure Code-reason]={"Success"}>}jobs)/Sum(jobs)),[Fund Custom Name])

Basically wrapping the code in

Aggr({1} Rank(

and

),[Fund Custom Name])

This appeared to be working for converting all my master measures (many of which have much more complicated set analysis) until I tried to add the time selection, which the formula ignores.

I believe the identifier {1} is preventing recalculation based on the selection but if I don't include the {1} then when I select [Fund Custom Name] it isn't locked and stays at 1.

Any ideas? Ideally some code I can use to 'wrap' around all my existing master measures?

Cheers

Thomas

[pradosh_thakur]

create a table 

use 

dimension: 

[Fund Custom Name]

Measure:

 Rank(sum({<[Failure Code-reason]={"Success"}>}jobs)/Sum(jobs))

Use the mode, format option i the rank function depending upon how you want to see the rank 

https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/Ranki...

-Pradosh

[thomasmercer]

Thanks for the reply Pradosh, but still not quite getting the result I want.

I created the table and the Rank is correctly calculating based on [Fund Custom Name], however I want the user to be able to click into individual [Fund Custom Name] records and be able to see several calculated 'ranked' measures (not just the one I gave as an example) under that record + some other calculated measures on the dashboard.

Once a user clicks on the client, it just always ranks as 1/1 rather than the 1/25 before. Looking for a way for it to 'lock' the relative value to [Fund Custom Name] but 'unlock' against the time dimension, if that makes sense.

Any ideas?

Cheers

Thomas

[pradosh_thakur · Accepted solution]

aggr(Rank(sum({<[Failure Code-reason]={"Success"}>}jobs)/Sum(jobs)),[Fund Custom Name]) 

or 

aggr({<[Fund Custom Name]>}Rank(sum({<[Failure Code-reason]={"Success"}>}jobs)/Sum(jobs)),[Fund Custom Name])  

[thomasmercer]

Thanks Pradosh - the second example seems to be doing what I need.

Completely confused as to how/why it works but no need to get bogged down on that!

Cheers