# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Please visit COVID-19 group to keep up the discussion. GO TO GROUP
Highlighted
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

Tags (2)
1 Solution

Accepted Solutions
Partner

## 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:

9 Replies
Highlighted
Honored Contributor

## Re: Rank within group

Hi,

May be simple table wih

Dimehsion: Office

Regards,

Andrey

Highlighted
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.

Highlighted
Honored Contributor

## Re: Rank within group

Hi Lukas,

try it:

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

beck

Partner

## 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:

Highlighted
MVP

## Re: Rank within group

try this expression

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

Highlighted
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?

Highlighted
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

 Country Regional Sum A a1 123 A a2 140 A a3 141 A a4 144 B b1 110 B b2 2 B b3 5 C c1 20 C c2 114

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

 Country Regional Sum A a4 144 B b1 110 C c1 20

or

 Country Sum A 144 B 110 C 20

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.

Highlighted
Partner

## Re: Rank within group

as dimension :  country

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

result:

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

Highlighted
New Contributor II

## Re: Rank within group

Hi,

if filter by Group='B' it doesn't work. Do you have a solution?

Thank you