Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ext_bay
New Contributor III

FirstSortedValue Aggr double values

Hi,

I want to create a textbox in which I want to display the name of the individual whose name occurs most often. To do this I use below expression in which 'name' is the column in which the name of the individual is displayed:

FirstSortedValue(name, -Aggr(count([name]), name))

This generates what I am looking for but as soon as there are multiple names with the same max count, the textbox goes empty, indicating that something goes wrong. Any idea how to fix this?

1 Solution

Accepted Solutions
MVP
MVP

Re: FirstSortedValue Aggr double values

If you have multiple and you would want to pick Max(Timestamp), then this

FirstSortedValue(name, -Aggr((Count([name]) + Max(Timestamp)/1E10), name))

12 Replies
shraddha_g
Honored Contributor III

Re: FirstSortedValue Aggr double values

Did you try

Concat(Distinct FirstSortedValue(name, -Aggr(count([name]), name)),',')

MVP
MVP

Re: FirstSortedValue Aggr double values

May be try this

Concat(DISTINCT Aggr(If(Count(Name) = Max(TOTAL Aggr(Count(Name), Name)), Name), Name), ', ')

ext_bay
New Contributor III

Re: FirstSortedValue Aggr double values

Thanks for the response! Unfortunately it is not working, 'nested aggregations not allowed'.

ext_bay
New Contributor III

Re: FirstSortedValue Aggr double values

This one works! Now it displays all top values though, is it possible to just show the most recent one, so sort on timestamp for example?

MVP
MVP

Re: FirstSortedValue Aggr double values

May be this

FirstSortedValue(name, -Aggr((Count([name]) + Timestamp/1E10), name))

MVP
MVP

Re: FirstSortedValue Aggr double values

This is assuming that you have a single timestamp for any given name

MVP
MVP

Re: FirstSortedValue Aggr double values

If you have multiple and you would want to pick Max(Timestamp), then this

FirstSortedValue(name, -Aggr((Count([name]) + Max(Timestamp)/1E10), name))

ext_bay
New Contributor III

Re: FirstSortedValue Aggr double values

Awesome! Could you explain this one to me?

MVP
MVP

Re: FirstSortedValue Aggr double values

Earlier you were looking for firstsortedvalue (in descending order) based on just the count(Name), I added a little portion of Timestamp by adding Max(Timestamp)/1E10 to determine the sort order