topic Re: How to display most common value in a dimension? in App Development

How to display most common value in a dimension?

QlikToFindOut — Wed, 27 Apr 2022 15:29:12 GMT

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

bowl

fork

spoon

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

Thanks!

Re: How to display most common value in a dimension?

QlikToFindOut — Wed, 27 Apr 2022 15:53:29 GMT

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.

Re: How to display most common value in a dimension?

QlikToFindOut — Wed, 27 Apr 2022 16:01:52 GMT

All I see is this with that exact formula.

Re: How to display most common value in a dimension?

QlikToFindOut — Wed, 27 Apr 2022 16:06:52 GMT

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.

Re: How to display most common value in a dimension?

QlikToFindOut — Wed, 27 Apr 2022 17:05:54 GMT

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

Re: How to display most common value in a dimension?

QlikToFindOut — Wed, 27 Apr 2022 17:33:38 GMT

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.

Re: How to display most common value in a dimension?

vinieme12 — Thu, 28 Apr 2022 01:38:09 GMT

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

Re: How to display most common value in a dimension?

QlikToFindOut — Thu, 28 Apr 2022 14:19:06 GMT

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

Re: How to display most common value in a dimension?

Ursa_Rex — Thu, 27 Feb 2025 11:27:44 GMT

@vinieme12 Hi I found this thread because this is exactly the same thing I am struggling with. But my Problem is your solution

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

is still returning blanks for me, in fact it has made all the values blank/null. Do you have any advice on this please?