Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
TKendrick20
Partner - Specialist
Partner - Specialist

Concatenate Results if FirstSortedValue is a Tie

I have a text box with the following formula in it:

FIRSTSORTEDVALUE([Response Text],-AGGR(COUNT({<[Question ID]={'1'}>} [Response Text]),[Response Text]))


Which returns "Honesty" when there is a single value as the max.

Response TextCount

Honesty

13
Bravery11
Reliability8

But returns null ("-") when there is a tie at the top.

Response TextCount
Honesty13
Bravery13
Reliability6

How can I make the text box return something like "Honesty, Bravery"?

1 Solution

Accepted Solutions
sunny_talwar

My bad, try this

=Concat(DISTINCT Aggr(If(Count({<[Question ID] = {1}>}[Response Text]) = Max(TOTAL Aggr(Count({<[Question ID] = {1}>}[Response Text]), [Response Text])), [Response Text]), [Response Text]), ', ')

View solution in original post

7 Replies
sunny_talwar

I would may be try it like this

Concat(DISTINCT Aggr(If(Count({<[Question ID] = {1}>} [Response Text]) = Max(TOTAL Aggr(Count({<[Question ID] = {1}>} [Response Text]), [Response Text])), [Response Text]), ', ')

sunny_talwar

My bad, try this

=Concat(DISTINCT Aggr(If(Count({<[Question ID] = {1}>}[Response Text]) = Max(TOTAL Aggr(Count({<[Question ID] = {1}>}[Response Text]), [Response Text])), [Response Text]), [Response Text]), ', ')

gsbeaton
Luminary Alumni
Luminary Alumni

Sorry this is a wild and untested suggestion, but could you try wrapping the entire expression in Concat()? 

If - is a result of multiple values being returned which cannot be displayed in a single cell, concat() will help.  If it is a result of the function returning an internal null, then concat() will do nothing.

It's worth a try...

gsbeaton
Luminary Alumni
Luminary Alumni

Ha, @Sunny, I had exactly the same idea...

TKendrick20
Partner - Specialist
Partner - Specialist
Author

I did try that, but I see an error: "Nested aggregation not allowed"

TKendrick20
Partner - Specialist
Partner - Specialist
Author

Nice, that works! Thank you!

joanaleao
Contributor
Contributor

I have this expression that works fine but only returns  1 value if is a tie.

FirstSortedValue( distinct Artigo_Categoria, -Aggr($(fVendaDCCat), Artigo_Categoria, Cliente_Codigo))

How can i change it to return all tied categories?

 

Thank you.