The MAX aggregation function returns the maximum value from a line item in a source module.
For example, you can use the MAX aggregation function to show the most recent employee start date in different departments and cities.
Syntax
Source[MAX: Mapping, MAX: Mapping 2, etc.]
Arguments
| Argument | Data type | Description |
| Source | Number, date, time period | The line item to search for the maximum value. The maximum value for a number is the highest value, and the maximum value for a date or time period is the most recent date or time period. |
| Mapping | List | The line item to use as search criteria. If you want to provide multiple criteria, you can repeat this argument. |
The MAX aggregation function returns a result of the same data type as the Line item argument.
Constraints
- The list that is used to format the mapping line item in the source module must be a dimension of the target line item that uses the MAX aggregation function.
- The dimensions of the mapping line item must also appear in the source line item.
- You can reference the Users list with the MAX function. However, you cannot reference specific users in the Users list as this is production data, which can change and make your formula invalid.
Examples
In this example, the Employee Details module has line items on columns, and the Employees list on rows. The module shows employee start months and city locations.
The Start Month line item has a time period data type. The City line item has a list data type, and is formatted on the City list.
| Start month | City | |
| Employee A | Jan 21 | London |
| Employee B | Jul 21 | New York |
| Employee C | Jul 21 | San Francisco |
| Employee D | Sept 21 | Edinburgh |
| Employee E | Feb 21 | New York |
| Employee F | Apr 21 | London |
| Employee G | Aug 21 | Edinburgh |
Below, the New employees FY21 module has the Most recent hire line item on rows, and the City list on columns.
The formula uses the MAX aggregation function to show the most recent hire in each city.
| London | Edinburgh | San Francisco | New York | |
Most recent hire
| Apr 21 | Sept 21 | Jul 21 | Jul 21 |
Example with two mappings
Another example uses the Employee Details module as above, but includes an additional Department line item. Department has a list data type, and is formatted on the Department list.
| Start month | Department | City | |
| Employee A | Jan 21 | HR | London |
| Employee B | Jul 21 | Finance | New York |
| Employee C | Jul 21 | Sales | San Francisco |
| Employee D | Sept 21 | Marketing | Edinburgh |
| Employee E | Feb 21 | Finance | New York |
| Employee F | Apr 21 | HR | London |
| Employee G | Aug 21 | Marketing | Edinburgh |
Below, the New employees FY21 module has the Most recent hire line item on pages, the Department list on rows, and the City list on columns.
The formula in the line item uses the MAX aggregation function to show most recent hires in different departments and cities: Employee Details.Start month[MAX: Employee Details.City, MAX: Employee Details.Department].
For example, the latest hire in the HR department in London was Apr 21.
| London | Edinburgh | San Francisco | New York | |
| HR | Apr 21 | |||
| Finance | Jul 21 | |||
| Sales | Jul 21 | |||
| Marketing | Sept 21 |
