Filtering a selection by Rank

[lee_]

Greetings All, 

I've got a simple inline table which i can rank by cost in set analysis. Via set analysis and not script I'm trying to filter the table down to the selected dimension Project and the 2 rows above and beneath the current selection based on rank. 

So, in the table below I have the full table with Project and Cost being scripted and rank achieved by 

aggr(rank(sum(Cost),4,1), Project)

Project	Cost	Rank
H	80	1
G	70	2
F	60	3
C	50	4
B	40	5
D	30	6
E	20	7
I	20	7
A	10	9

Id like to achieve the following table if i select Project 'D' , the rest of the rows can be omitted

Project	Cost	Rank
C	50	4
B	40	5
D	30	6
E	20	7
I	20	7

Tried using the following 

If(Aggr(Rank(Sum({1} Cost), 1), Project) >=Only(TOTAL Aggr(Rank(TOTAL Sum({1<Project>} Amount)), Project))-2

and

Aggr(Rank(Sum({1} Amount), 1), Colour) <= Only(TOTAL Aggr(Rank(TOTAL Sum({1<Project>} Amount)), Project))+2, Project)

And this just moves the 'Project' to the top of the table and sort the other 'Projects ' alphabetically. 

Any ideas anyone?

thanks in advance 

[Oleg_Troyansky · Accepted solution]

Hi Lee,

Short of solving this tricky puzzle, I have some ideas for you to consider:

- Using Set Analysis {1} inside of an AGGR() wouldn't work as expected. You should populate the same set analysis at the AGGR level, like this:

Aggr({1}  Rank(TOTAL Sum({1} Amount)), Project)

- Disregarding Project selection within Set Analysis {1} is redundant - {1} ignores any selections

- TOTAL within rank is only helpful with multiple dimensions, otherwise it's a moot point.

For a while, I couldn't wrap my mind around this problem - how do I compare the global rankings with the rank of the selected project, and how do I cause the table to show all projects, despite the selection. I was about to suggest Alternate States, and then I actually found the solution. Here goes:

1. I defined a variable vSelectedRank, to capture the value of the selected project's rank:

=only({1 <Project=P({$} Project)>} AGGR( {1} rank(sum(Cost), 4, 1), Project) )

2. Then, in the Dimension, I compare the ranks like this:

={1} AGGR(
IF(rank( sum(Cost), 4, 1) >= $(vSelectedRank) -2 and
rank( sum(Cost), 4, 1) <= $(vSelectedRank) +2
,
only(Project))
, Project)

3. In the measure, I used the following:

{1} sum(aggr( rank(Sum(Cost),4,1), Project))

And then I can see only 5 projects in the table after selecting 😧

Now, this solution works well when one project is selected. With no selections, it only shows the first 3 projects. You'll need to add some conditions to overcome that. For example, you can have 2 variables - min and max, and compare your ranks to both of them. Then, you can capture all ranks between min-2 and max+2, and that should work universally.

Allow me to invite you to my training session on Set Analysis and AGGR() at the Masters Summit for Qlik in Hamburg or at my Qlik Expert Class in Vienna - see links in my signature.

Cheers,

Oleg Troyansky

[lee_]

Thank you Oleg -that's certainly put me on the right path

ps I loved your book

[Oleg_Troyansky]

Thank you! It's so great to get feedback like this about my book! 

Cheers,

Oleg