Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
Partner
Partner

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

Re: Concatenate Results if FirstSortedValue is a Tie

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]), ', ')

6 Replies

Re: Concatenate Results if FirstSortedValue is a Tie

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]), ', ')

Re: Concatenate Results if FirstSortedValue is a Tie

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

Re: Concatenate Results if FirstSortedValue is a Tie

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

Re: Concatenate Results if FirstSortedValue is a Tie

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

Partner
Partner

Re: Concatenate Results if FirstSortedValue is a Tie

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

Partner
Partner

Re: Concatenate Results if FirstSortedValue is a Tie

Nice, that works! Thank you!