This function will pull the value of a field from the a record across to the parent record, after evaluating all child records that meet the criteria specified in the filter query argument, and where the records have been sorted according to the field specified.
MAXIMUM_BYINDEX( <field_name>, Index [, <orderby field name>] [, <filter_query>] [,<resolution_level>] )
-
field_name: The field to return the value from
-
Index: 0 or 1 can be used to ensure that the Maximum value is returned. Specifying 2 returns the 2nd largest, 3 the 3rd largest and so on.
-
orderby field_name: Child records will be sorted in ascending order based on the field specified in this parameter
-
filter_query: A query string representing the domain of records to include
-
resolution_level: A string representing the table to resolve the count, only required when used in dynamic query calculations.
Example: Create field at CUSTOMER level to return PRODUCT CODE of the 2nd most expensive PURCHASE made in 2004.
MAXIMUM_BYINDEX( [Purchase.Product Code], 2, [Purchase.PurchasePrice],[Purchase.YEAR BANDING] = "2004"