topic Re: Using Rank and ignoring selections in App Development

Using Rank and ignoring selections

Tue, 16 May 2017 16:03:52 GMT

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!

Re: Using Rank and ignoring selections

Anonymous — Tue, 16 May 2017 16:17:19 GMT

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

Re: Using Rank and ignoring selections

swuehl — Tue, 16 May 2017 16:45:02 GMT

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.

Re: Using Rank and ignoring selections

Tue, 16 May 2017 17:42:05 GMT

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

Re: Using Rank and ignoring selections

Tue, 16 May 2017 17:46:25 GMT

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

Re: Using Rank and ignoring selections

Tue, 16 May 2017 17:49:16 GMT

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?

Re: Using Rank and ignoring selections

swuehl — Tue, 16 May 2017 18:21:33 GMT

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

Re: Using Rank and ignoring selections

Tue, 16 May 2017 18:24:29 GMT

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

Re: Using Rank and ignoring selections

Tue, 16 May 2017 18:57:46 GMT

Here we go...

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

Re: Using Rank and ignoring selections

swuehl — Tue, 16 May 2017 19:21:43 GMT

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.

Re: Using Rank and ignoring selections

Tue, 16 May 2017 21:53:44 GMT

The Average Jobs for the Top 5 consultants should be 43.4

When you select an OFFICE or a REGION on the left the Average Jobs for the Top 5 consultants should stay the same; instead they change to a different number.

Note that if you select consultants from the table (clicking on CONSULTANT_ID) the Average Jobs for the Top 5 consultants remains what it should be (43.4). Different behaviour!!

Re: Using Rank and ignoring selections

swuehl — Tue, 16 May 2017 22:25:56 GMT

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