Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Using Rank and ignoring selections

I'd like to use Rank but ignore my selections.

I am using the following expression:

AVG(Total {<CONSULTANT_ID = {"=Rank(SUM(TS_CON_HR_FEES_SUM_L123))<=5"}>}[TS_CON_OT_JOBS_CNT_L123])

which works perfectly until I make selections and then the rank works on the selected consultants - I want it to continue to use the whole data set. I know {1} denotes the whole data set ignoring selections but I don't know where to put it in the expression above.

As an extension I would like to perhaps retain some dimensions that have been used. For example there is an OFFICE dimension and if an OFFICE has been selected then I'd like to use that selection before I rank my top 25.

Please help me - I have been battling all day with this!

1 Solution

Accepted Solutions
MVP
MVP

Re: Using Rank and ignoring selections

try

=AVG(total {1<CONSULTANT_ID = {"=Rank( SUM ({<CONSULTANT_ID=, A_CON_OT_REGION=, A_CON_OT_OFFICE= >}TS_CON_HR_FEES_SUM_L123))<=5"} >} [TS_CON_OT_JOBS_CNT_L123])

11 Replies

Re: Using Rank and ignoring selections

AVG(Total { 1 <CONSULTANT_ID = {"=Rank(SUM(TS_CON_HR_FEES_SUM_L123))<=5"}>}[TS_CON_OT_JOBS_CNT_L123])

MVP
MVP

Re: Using Rank and ignoring selections

The record set for the advanced search

=Rank(SUM(TS_CON_HR_FEES_SUM_L123))<=5


is not controlled by the set identifier used in the Avg() aggregation, so you need to apply another set expression to the Sum():

=Rank(SUM({1} TS_CON_HR_FEES_SUM_L123))<=5


If you want to consider the OFFICE selections in the rank, then there are two options:

=Rank(SUM({1<OFFICE = $:Smiley SurprisedFFICE>} TS_CON_HR_FEES_SUM_L123))<=5


or if you don't like to specify the field selections to consider, but just ignore the selection in Consultant_ID:

=Rank(SUM({<CONSULTANT_ID= >} TS_CON_HR_FEES_SUM_L123))<=5

Not sure if you also need to adapt the set expression for the average, that depends how you want your avg calculation filtered.

Not applicable

Re: Using Rank and ignoring selections

That doesn't quite work. I have a field on my consultant table called REGION. This is linked to a look up table for region. If I include the field region as a selection box on the app when I select it this expression re-calculates for the top 25 for the region selected; not what I want

Not applicable

Re: Using Rank and ignoring selections

Following your first suggestion appears to work at first look but then when I make a selection on REGION as described above it seems to become an invalid expression as it returns NULLs

Not applicable

Re: Using Rank and ignoring selections

Your last suggestion looked good as well but now selecting REGION as described above recalculates the expression to top 25 for the selected REGION!

Pulling hair out!

I read somewhere that Rank does not take set expressions but that Aggr does? But how do I add Aggr to my expression?

MVP
MVP

Re: Using Rank and ignoring selections

Could you post a small sample application and your requested results when doing specific selections?

Not applicable

Re: Using Rank and ignoring selections

I'll try. Some of the data is company confidential so need to be careful!

Not applicable

Re: Using Rank and ignoring selections

Here we go...

The table on the "scratch" tab has a column with the expression...

MVP
MVP

Re: Using Rank and ignoring selections

Sure, you don't need to post confidential data.

Just set up some mock up records and create a data model that shows your real setting and relation between tables.

Community Browser