MODE

For each parent record this cross-table function will return the most frequent value of a field from a child table. So for example you can establish the most frequently purchased product.

MODE( <field_name> [, Index [, <filter_query> [, tie_value]]] )

  • Field_name: The field to return the value from.

  • Index: 0 or 1 being the most popular, 2 being the second most popular. -1 being the least popular,-2 being the second least popular etc. If not supplied, 1 is the default value used. When the index exceeds the number of modal groups then a null is returned.

  • Filter_query: Allows the records included in the calculation to be filtered. If not supplied then all records are included in the calculation.

  • Tie_value: It is possible that two or more field values may have the same frequency. A user specified value can be returned in such cases. If no tie value is supplied the function will return a single value from the modal group.

Example: create a field at the CUSTOMER table level to return the 2nd most popular product code purchased in 1992 by each customer.

MODE( [PRODUCT.PRODUCT_CODE], 2 ,[PRODUCT.PURCHASE YEAR] = "1992","TIED" )