Rank Dimension Using Concat Aggr If Rank

[Paneze]

Hi everyone, 

I have a dimension called trust, and a measure called target met. I have 5 distinct value sin the Trust dimension. I would like to rank the values in trust from highest Target Met to Lowest in a text box so that it looks like this.

1. A

2. B

3. C

4. E

5. F

A Qlik community member kindly shared this expression with me

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

But the thread was marked as solved before I tried the formula.

Unfortunately this creates a gap, something to do with tied values. The text box looks instead something like this

1.A

2.B

3.C

4.D

5.

6.E

Is there a way to get rid of this gap?

Thank you

[MayilVahanan · Accepted solution]

Hi @Paneze 

I think, you have null values or missing value in your data model for Trust, In that case, might be, below expression helps you

=concat(aggr(if( rank(sum({<TRUST={"=Len(Trim(TRUST))>0"}>}[Target met]),4)<=6,rank(sum({<TRUST={"=Len(Trim(TRUST))>0"}>}[Target met]),4)&'. '&TRUST), TRUST),chr(10))

If not, please attach sample data 

[Paneze · Accepted solution]

Thank you so much it worked !! Didn't consider that some values might have this field as blank as there is an 'Other' category.

[MayilVahanan]

Happy that, you've learnt new way for checking the issue 😀