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.

Source[MAX: Mapping, MAX: Mapping 2, etc.]

ArgumentData typeDescription
SourceNumber, 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.

MappingList

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.

  • 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.

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 monthCity
Employee AJan 21London
Employee BJul 21New York
Employee CJul 21San Francisco
Employee DSept 21Edinburgh
Employee EFeb 21New York
Employee FApr 21London
Employee GAug 21Edinburgh

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.


LondonEdinburghSan FranciscoNew York

Most recent hire

Employee Details.Start month[MAX: Employee Details.City]

Apr 21Sept 21Jul 21Jul 21

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 monthDepartmentCity
Employee AJan 21HRLondon
Employee BJul 21FinanceNew York
Employee CJul 21SalesSan Francisco
Employee DSept 21MarketingEdinburgh
Employee EFeb 21FinanceNew York
Employee FApr 21HRLondon
Employee GAug 21MarketingEdinburgh

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.


LondonEdinburghSan FranciscoNew York
HRApr 21


Finance


Jul 21
Sales

Jul 21
Marketing
Sept 21