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

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
swuehl
MVP
MVP

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

View solution in original post

11 Replies
Anonymous
Not applicable
Author

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

swuehl
MVP
MVP

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 = $::OFFICE>} 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
Author

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
Author

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
Author

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?

swuehl
MVP
MVP

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

Not applicable
Author

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

Not applicable
Author

Here we go...

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

swuehl
MVP
MVP

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.