3 Replies Latest reply on Jun 29, 2016 9:10 AM by coherentkris

# Mode (mathematical) calculation for products/line items

Folks a little question here, so I'd like to work out a most frequent order quantity (mode) for my invoice line items and to view that in the product page.

I understand that this would be the right calculation but I'm not experienced enough with SQL calcs to be able to plug in the correct data. Can somebody help me or at least steer me in the right direction?

// 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

• ###### 1. Re: Mode (mathematical) calculation for products/line items

What you have is the definition for a custom function from FileMaker Custom Function:Mode ( ValueList ; Value; Cnt )

it just so happens that this is a recursive custom function so ...

Go ahead and create a new custom function.

Name it Mode.

Past in the calculation.

Add parameters ValueList, Value, Cnt and save.

Now this function is available to the calculation engine anywhere the calc engine is exposed.

This is another option to consider

www.nightwingenterprises.com/Resources/ModeCalc.pdf

• ###### 2. Re: Mode (mathematical) calculation for products/line items

Awesome that was a lot simpler than I thought!

Followed the above instructions and created a calc field in the Products thus:

Mode ( List (T15c_products_INVOICE_LINE_ITEMS||id_products|::Quantity) ; "" ; "" )