Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Paneze
Contributor II
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)

2.CAD

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

Accepted Solutions
GaryGiles
Specialist
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))

View solution in original post

2 Replies
GaryGiles
Specialist
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))

View solution in original post

Paneze
Contributor II
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?