Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

lwestmaas
New Contributor III

Rank within group

Hi,

I'm trying to produce a table that shows ranks within group. Take the data below:

OfficeGroupScore
1A5
2A9
3A1
4B10
5B19
6B5
7B3
8B4

I haven't been able to produce rank within group. For example, the rank for Office 2 should be 1 because they are the top office in Group A even though there are two offices in Group B with a better score.

I need to be able to do this even if Group is not part of the visualization, if that makes a difference.

I'm guessing the solution requires AGGR but I haven't been able to get it to work.

Can anyone help? Thanks in advance!

Lucas

1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor

Re: Rank within group

No Beck, that won't achieve what's he's looking for:

this will do it:

Aggr(Rank(Sum(Score)),Group,Office)

result:

Capture.PNG

8 Replies
ahaahaaha
Honored Contributor

Re: Rank within group

Hi,

May be simple table wih

Dimehsion: Office

Exprssion: Rank(Sum(Score), 4)

Regards,

Andrey

lwestmaas
New Contributor III

Re: Rank within group

It looks like that returns the rank for the whole table, not within group. In my example, that would return a rank of 3 for office 2.

beck110979
Valued Contributor III

Re: Rank within group

Hi Lukas,

try it:

Aggr(Rank(Sum({1}Score)),Office) or Aggr(Rank(Sum({1}Score)),Group)

beck

OmarBenSalem
Esteemed Contributor

Re: Rank within group

No Beck, that won't achieve what's he's looking for:

this will do it:

Aggr(Rank(Sum(Score)),Group,Office)

result:

Capture.PNG

Re: Rank within group

try this expression

=rank(sum(total <Office,Group>Score),0,1)



lwestmaas
New Contributor III

Re: Rank within group

Omar - thanks, that worked! I swear I tried that yesterday but apparently not. I'm having a related issue now - maybe you can help? This formula correctly assigns rank to all selections but does not assign a value to non-selected values. This is important because my visualization basically constructs comparison groups based on the selection - e.g. if you select Office 1, then the data for Offices 2 and 3 (the other group A offices) will also appear for context. Your formula correctly assigns rank 2 to Office 1 when Office 1 is selected, but does not show rank for Offices 2 or 3. Set analyses don't appear to make a difference here.

I've tried all three of:

aggr({<1>} rank(sum(Score)),Group,Office)

aggr(rank(sum({<1>} Score)),Group,Office)

aggr({<1>} rank(sum({<1>} Score)),Group,Office)


When I just use rank(sum({<1>} Score)) I am getting values to appear, so it seems to be something to do with AGGR even though I am specifying to ignore selections there. Any ideas?

tanykozel
New Contributor II

Re: Rank within group

Dear Omar,

Maybe you can help me on this.

It's some another from guys problem

So, I have this table

   

CountryRegionalSum
Aa1123
Aa2140
Aa3141
Aa4144
Bb1110
Bb22
Bb35
Cc120
Cc2114

And shoud show only  the the list of countries  and only 1 region sales  in each country with first  rank in this country.

it should look like this

   

CountryRegionalSum
Aa4144
Bb1110
Cc120

or

  

CountrySum
A144
B110
C20

I hope you can help on this. This principle is very useful, when you have a lot of regions, periods and sums and you should show only top of performance.

OmarBenSalem
Esteemed Contributor

Re: Rank within group

as dimension :  country

as an expression : aggr(max( total<Country> Sum),Country)

result:

Capture.PNG

if you want to keep the regional dimension; use the same expression and in the add-on tab; uncheck show null values:

Capture.PNG

Community Browser