Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to show "Most Common" result

Hi,

I am trying to create a pivot table of the top marketing regions. Customers in these regions can pick 1 of 5 hospitals... I want to count the frequency of hospitals within each marketing region, but display only the top frequency hospital. Is this possible?

3 Replies
whiteline
Master II
Master II

Hi.

I think you can do it using two features: calculated dimension and rank() function.

=aggr(if(rank(rankexpression)=1, Hospital), Hospital)

Also if you can set the rankexpression as first expression the dimension limits feature exists.

mike_garcia
Luminary Alumni
Luminary Alumni

You'll probably find this article useful: http://iqlik.wordpress.com/2013/01/22/computing-the-mode-in-a-multimodal-dataset/

In simple terms, you could use de Mode() function. However, there are a few "issues" you may find, which are discussed in the above article.

Kind regards,

Mike

Miguel García
Qlik Expert, Author and Trainer
trey_bayne
Partner - Creator
Partner - Creator

I literally ran in to this same issue today. Here's what I came up with,

FirstSortedValue( HospitalName, -aggr(Count(VisitedHospitalCount),HospitalName))

FirstSortedValue Sorts the first Expression (HospitalName) in the order of the Second Expression Aggr(Count(VisitedHospitalCount),HospitalName)). The '-' in front of it reverses the sort and gives me the max value.