topic Ranking Range in App Development

Ranking Range

RichardSmith — Tue, 15 Nov 2022 14:33:13 GMT

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

Re: Ranking Range

vinieme12 — Wed, 16 Nov 2022 02:35:36 GMT

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

Re: Ranking Range

RichardSmith — Wed, 16 Nov 2022 16:37:35 GMT

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!

Re: Ranking Range

vinieme12 — Thu, 17 Nov 2022 06:17:31 GMT

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 ?

Re: Ranking Range

RichardSmith — Mon, 21 Nov 2022 12:00:02 GMT

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!

Re: Ranking Range

vinieme12 — Tue, 22 Nov 2022 05:51:50 GMT

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

Re: Ranking Range

RichardSmith — Tue, 22 Nov 2022 11:01:16 GMT

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.

Re: Ranking Range

vinieme12 — Tue, 22 Nov 2022 12:15:21 GMT

As below

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

Re: Ranking Range

RichardSmith — Tue, 22 Nov 2022 12:23:36 GMT

Perfect, thank you