5 Replies Latest reply on Sep 22, 2013 2:20 PM by rmulder

Custom function for calculating mode?

Title

Custom function for calculating mode?

Post

I am trying to get a calculation to return the most common value (mode) for a field from a set of records.  For instance, if the values in eight records were 1,2,3,3,3,4,4,5 I would want the calculation to return '3'.  I assumed there would be a function or custom function to do this, but I cannot find one anywhere.  Can anyone offer advice?

Many thanks!

• 1. Re: Custom function for calculating mode?

I also could not find an obvious answer - it does not help that the term is Mode and mode is used by FMP for it's display types.

Brian Dunnings Custom Functions did not seem to have one

This FMP article is not about Mode but Unique Values
There may be an ExecuteSQL that would provide a Mode

Counting the Number of Unique Values in a Field
How do I count the number of unique values in a field?

• 2. Re: Custom function for calculating mode?

Hmmm, If Brian's site doesn't have one, Maybe I should upload this one to that site:

// Mode ( ValueList ; Value; Cnt )
//
// valueList    :  A return separated list of values. values can be text or number
// value         : Iterim value that passes the current mode candidate to the next recursive call of the function.
// Cnt            : The count of the items of the currently nominated value
//
Let ( c = ValueCount ( FilterValues ( ValueList ; GetValue ( valueLIst ; 1 ) ) ) ;
Case ( IsEmpty ( ValueList ) ; Value ; // return current data in Value if list is empty
c > Cnt ; Mode ( RightValues ( ValueList ; ValueCount ( ValueList ) - 1 ) ; GetValue ( ValueList ; 1 ) ; c ) ;
Mode ( RightValues ( ValueList ; ValueCount ( ValueList ) - 1 ) ; Value ; Cnt )
) // case
) // let

• 3. Re: Custom function for calculating mode?

Brilliant - this is exactly what I was looking for! Quite surprising that this wasn't already on the list of custom functions.  Thanks very much for the insightful replies and for posting the custom function.

When there is a tie between two values that occur equally commonly, either value may be returned, do I understand that correctly?

• 4. Re: Custom function for calculating mode?

It's been ages since I created that one in response to another post here in the forum. If I am mentally analyzing the code correctly, in cases of a "tie" the "leftmost" value (the first to be counted) is returned. A new value is selected and stored in the function parameter only if its count is greater than the currently selected value's count so a "tie" doesn't change the value passed in that parameter.

And I did go ahead and upload this one to the Brian Dunning site after confirming for myself that there isn't already such a function in place.

• 5. Re: Custom function for calculating mode?

Thanks again for your help, and for posting to Brian Dunning's site.  It's a very useful function.