- x: line item to search for a maximum value
- y: list item to use as criteria for the search
|Input Format||Output Format|
x: Line item
Matches the data format used by argument x
The function uses the following arguments:
- x: number, date, or time period-formatted source line item.
- y: list-formatted source line item.
Use MAX with the Users list
You can reference the Users list with the MAX function. However, you cannot reference specific users within the Users list as this is production data, which can change and make your formula invalid.
The function has the following constraints:
- Two parameters are required.
- The x parameter format must be the same in the source and result modules.
- The result or target line item must share a dimension with the list used to format the source y parameter line item.
- No Excel equivalent
MAX as an Aggregation Function
When used as an aggregation function, MAX returns the maximum value of the source line item x for data that matches criteria in a second line item formatted with list y.
Highest numerical value
In this example, a Pay Details source module uses an Employees list as Rows, and shows Pay and City for each employee. Two line items are on Columns: Pay is number-formatted and City is list-formatted on a City Location list:
A results module has City Location as a dimension on Columns. We can then use MAX in a Max Pay for City number-formatted result line item to show the maximum pay level at each city work location:
Pay Details.Pay[MAX:Pay Details.City]
Most Recent Date
If we add a Start Date date-formatted source line item to the Pay Details source module, we can use MAX in the same results module that has City Locations applied as a dimension on Columns.
The Latest Start Date for City date-formatted result line item returns the most recent start date for an employee at each city location:
Pay Details.Start Date[MAX:Pay Details.City]
Most recent date per location
We can add a Start Month time period source line item, formatted for Months, to the Pay Details source module.
We can use MAX in the same results module that has City Locations applied as a dimension on columns—the Latest Start Month for City time period-formatted for Months result line item returns the most recent employee start month at each city location:
Pay Details.Start Month[MAX:Pay Details.City]