Ranking Range

[RichardSmith]

Hi, 

I can create either a table or pivot table which has columns

Team,  Measure, Rank based on Measure

e.g.

Team 3,   15,   1

Team 1,   17,   2

Team 5,   25,   3

Team 4,   34,   4

Team 6,   54,   5

Team 2,   75,   6

If Team is selected as a filter, is there a way of just displaying  the selected Team and the Team above and below the selected Team.

We're looking to NPrint a report to various teams and show a snapshot  of their rankings against their nearest competitors, say 5 teams above and below their own ranking.   

Thanks

[vinieme12 · Accepted solution]

As below

load * inline [
Team,Amt,Rank
Team 3,15,1

Team 1,17,2

Team 5,25,3

Team 4,34,4

Team 6,54,5

Team 2,75,6
];
exit Script;

In Chart:

Dimension

=Team

Measures

Above 2

=if(rank(sum({1}Amt),4,2) >= if(getselectedcount(Team),sum(total aggr(distinct rank( sum({1}Amt),4,2),Team))-2,1)
and
rank(sum({1}Amt),4,2) <= sum(total aggr(distinct rank( sum({1}Amt),4,2),Team))
, rank(sum({1}Amt),4,2) )

Below 2

if(rank(sum({1}Amt),4,2) >= sum(total aggr(distinct rank( sum({1}Amt),4,2),Team))
and
rank(sum({1}Amt),4,2) <= if(getselectedcount(Team),sum(total aggr(distinct rank( sum({1}Amt),4,2),Team))+2,1)
, rank(sum({1}Amt),4,2) )

Selected Team 4 which is Rank 3 based on sum(Amt)

Above 2 showing teams with rank 1,2,3 

Below 2 showing teams with rank 3,4,5 

[RichardSmith]

Thanks for taking the time to investigate and reply, much appreciated. There's a couple more hurdles to get over I think. I was doing the ranking in the chart, but I can do that within the load script. In the table above, I would also need the Amt to be shown and also, when the team is selected to not show the Team where there are Nulls in both the Above 2 and Below 2. I tried it myself without success. I tried adding your code around the Team and setting it to Null, but it didn't work.

I guess another approach would be to try to replicate your Above 2 and Below 2 code also in the load script, and then the Team could be set to Null and ignored.  Pity there's nothing straight out of the box for this in Qlik! 

[vinieme12]

even in the above example i am not doing any calculations in the load script, the rank is calculated based on sum(Amt) 

Can you post a sample app ?

[RichardSmith]

Sorry, I am beginning to understand this a little more. Agreed, it can all be achieved on the chart. I'm struggling to replicate it with my own data. Here's an example ranking

aggr(Rank(-Sum({$<New_Stock={Yes},Team=>}Quantity)/Sum({$<Team=>}Quantity)),Team)

Could this be converted into a a similar Above 2, Below 2 code? I've tried to, but failed!

[vinieme12]

Above 2

=if(rank(-(Sum({<New_Stock={'Yes'},Team=>}Quantity)/Sum({<Team=>}Quantity)),4,2) >= if(getselectedcount(Team),sum(total aggr(distinct rank( -(Sum({<New_Stock={'Yes'},Team=>}Quantity)/Sum({<Team=>}Quantity)),4,2),Team))-2,1)
and
rank(-(Sum({<New_Stock={'Yes'},Team=>}Quantity)/Sum({<Team=>}Quantity)),4,2) <= sum(total aggr(distinct rank( -(Sum({<New_Stock={'Yes'},Team=>}Quantity)/Sum({<Team=>}Quantity)),4,2),Team))
, rank(-(Sum({<New_Stock={'Yes'},Team=>}Quantity)/Sum({<Team=>}Quantity)),4,2) )

Below 2

if(rank(-(Sum({<New_Stock={'Yes'},Team=>}Quantity)/Sum({<Team=>}Quantity)),4,2) >= sum(total aggr(distinct rank( -(Sum({<New_Stock={'Yes'},Team=>}Quantity)/Sum({<Team=>}Quantity)),4,2),Team))
and
rank(-(Sum({<New_Stock={'Yes'},Team=>}Quantity)/Sum({<Team=>}Quantity)),4,2) <= if(getselectedcount(Team),sum(total aggr(distinct rank( -(Sum({<New_Stock={'Yes'},Team=>}Quantity)/Sum({<Team=>}Quantity)),4,2),Team))+2,1)
, rank(-(Sum({<New_Stock={'Yes'},Team=>}Quantity)/Sum({<Team=>}Quantity)),4,2) )

[RichardSmith]

Fantastic, it did the trick, thanks again for helping with this and posting a solution. One final question, is there an easy way to combine Above 2 and Below 2 into one column. It's not crucial as I think I should be able to manipulate this in NPrinting.

[vinieme12 · Accepted solution]

As below 

=if(rank(-(Sum({<New_Stock={'Yes'},Team=>}Quantity)/Sum({<Team=>}Quantity)),4,2) >= if(getselectedcount(Team),sum(total aggr(distinct rank( -(Sum({<New_Stock={'Yes'},Team=>}Quantity)/Sum({<Team=>}Quantity)),4,2),Team))-2,1)

and

rank(-(Sum({<New_Stock={'Yes'},Team=>}Quantity)/Sum({<Team=>}Quantity)),4,2) <= if(getselectedcount(Team),sum(total aggr(distinct rank( -(Sum({<New_Stock={'Yes'},Team=>}Quantity)/Sum({<Team=>}Quantity)),4,2),Team))+2,1)
, rank(-(Sum({<New_Stock={'Yes'},Team=>}Quantity)/Sum({<Team=>}Quantity)),4,2) )

[RichardSmith]

Perfect, thank you