Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
QlikToFindOut
Creator
Creator

How to display most common value in a dimension?

Hello,

I am struggling a bit with this function and examples I've found in the Qlik community don't seem to be working for me.

My function is as follows:

FirstSortedValue([Kitchen Items], -Aggr(Count([Kitchen Items]), [Kitchen Items]))

The chart object I am using is a Textbox in Qlik Sense, but all it displays is just a blank object. Not sure where I'm messing this up.

The field looks something like this:

Kitchen Items
knife
knife 
knife
bowl
bowl
fork
spoon

 

The function should return knife. Not sure where I'm messing up and any help is greatly appreciated.

Thanks!

Labels (3)
1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

Two Options,

1) use Mode(), again  this function can only return one value, so if there is a tie this will return null, but is the most straight forward expression

= Mode(KitchenItems) 

 

2) This will return concatenated list of more than one item incase there is a tie

=concat(distinct aggr(if(rank(count(KitchenItems))=1,KitchenItems),KitchenItems),',')

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

7 Replies
QlikToFindOut
Creator
Creator
Author

I think I found out what might be causing it. There are an incredible number of null values in the table itself.

Is there a way to suppress those?

 

EDIT: Nevermind, the nulls were not causing the problem. I'm still getting a blank value and not sure why this is happening.

QlikToFindOut
Creator
Creator
Author

leonling_0-1651075302500.png

 

 

All I see is this with that exact formula.

QlikToFindOut
Creator
Creator
Author

An update is that when I create a filter pane with the [Kitchen Items] field and select something, the function works. But that's not the solution I'm going for.

QlikToFindOut
Creator
Creator
Author

Still not working. I just realized the table didn't come out the way I was expecting it to come out. I don't think this would affect it, but let me know if that's not the case.

FieldA Kitchen Items FieldB
valueA knife valueD
valueB knife valueD
valueC knife valueD
valueA fork valueE
valueB knife valueE

 

QlikToFindOut
Creator
Creator
Author

I found the problem. The function works, it was because I didn't realize that if there's a tie, it won't display anything. 

vinieme12
Champion III
Champion III

Two Options,

1) use Mode(), again  this function can only return one value, so if there is a tie this will return null, but is the most straight forward expression

= Mode(KitchenItems) 

 

2) This will return concatenated list of more than one item incase there is a tie

=concat(distinct aggr(if(rank(count(KitchenItems))=1,KitchenItems),KitchenItems),',')

 

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
QlikToFindOut
Creator
Creator
Author

Thanks, this worked! Still trying to digest solution #2, it's quite the expression.