New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

8 Replies
Honored Contributor

Re: Rank within group

Hi,

May be simple table wih

Dimehsion: Office

Regards,

Andrey

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.

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

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:

MVP

Re: Rank within group

try this expression

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

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?

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.

Esteemed Contributor

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: