Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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))
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?