# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for
Did you mean:
Contributor II

## How to Rank Dimension Values by a Measure using First Order Value or If + Aggr + Concat

Hello all,

I have scoured the whole of Qlik community but haven't found something that works yet 😞

I have a dimension let's call it 'Provider Group' which consists of 5 distinct values.

I then have two measures that I would like to combine into a single metric that will be used to rank the provider group top to bottom, from highest to lowest in a text box.

Metric 1: Target Met

Metric 2: Total

Calc Metric: sum(Target Met)/Sum(Total) lets call this 'Percentage Compliance'

1. ABC (This has the highest Percentage Compliance)

3. LED

4. AAB

5. BAC (This has the lowest Percentage Compliance)

When I used a formula similar to this :

=concat(aggr(if( rank(sum([Target met]),4)<=6,TRUST), TRUST))

It would rank the Provider groups, but didn't give me the rank number (1.ABC, <new line> 2.CAD, etc)

When I used First Sorted value, and did a line of code for each entry in the ranking there would be null values/duplicates. I did try and use the distinct Keyword, but this didn't resolve the issue unfortunately.

Thank you

Labels (8)

• ### Rank

1 Solution

Accepted Solutions
Specialist

Is TRUST the Provider Group field?  If the expression you gave produced the correct ranking and you simply want to add the rank number and a line feed, you could use the following:

=concat(aggr(if( rank(sum([Target met]),4)<=6,rank(sum([Target met]),4)&'. '&TRUST), TRUST),chr(10))

2 Replies
Specialist

Is TRUST the Provider Group field?  If the expression you gave produced the correct ranking and you simply want to add the rank number and a line feed, you could use the following:

=concat(aggr(if( rank(sum([Target met]),4)<=6,rank(sum([Target met]),4)&'. '&TRUST), TRUST),chr(10))

Contributor II
Author

Hi Gary, I tried your solution. It is ranking the group but there is a gap at point 5.

1.ABC

2.BCA

3.DEF

4.AAB

5.

6.BBA

How would one resolve this?

Tags
Community Browser