Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

using aggr to give me least values

Hello,

I currently have the following expression to rank the top five values in a field

=concat(distinct IF(aggr(rank(sum(Field1)),[Field2])<=5,[Field2]), CHR(13),-aggr(sum([Field1]),[Field2]))

However, I am looking to get a text object to show the opposite with the smallest 5 values in a field. Any ideas of how to tweak the function to show that?

Thanks

5 Replies
sunny_talwar

Try this maybe:

=Concat(Distinct If(Aggr(Rank(-Sum(Field1)),[Field2])<=5,[Field2]), CHR(13),-Aggr(-Sum([Field1]),[Field2]))



Best,

Sunny

Gysbert_Wassenaar

Multiply sum(Field1) by -1: rank(-sum(Field1))


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

Thanks, but this produced a lot more than 5 values

Anonymous
Not applicable
Author

Appears to be similar to what is mentioned above and is producing a lot more than the 5 values I am trying to limit it to

sunny_talwar

Do you have a lot of 0's in your aggregated Field1 may be?? If you that's true the rank of all 0 will be 1 and will show up in the minimum.

Best,

Sunny