How to show "Most Common" result

[#1]

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?

[whiteline]

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]

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

[trey_bayne]

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.