Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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),',')
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.
All I see is this with that exact formula.
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.
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 |
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.
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),',')
Thanks, this worked! Still trying to digest solution #2, it's quite the expression.