The MAX function returns the maximum from a set of values. For a number, it returns the maximum value. For a date, it returns the latest date.

For example, you can use the MAX function to force negative numbers to be zero without affecting positive numbers.

MAX(Value to compare, Value to compare 2, [etc.])

ArgumentData typeDescription
Value to compare (required)Number, date

The values to search for the maximum value. Returns the highest number or most recent date.

All values must be of the same data type.

This argument can be repeated to provide multiple values for comparison. It must be provided a minimum of two times.

The MAX function returns a result of the same data type as the Value to compare arguments.

MAX(P1, 100, P2 / 2)

This example returns the highest value out of:

  • The P1 line item
  • The number 100
  • The P2 line item divided by two

MAX

In this example, the MAX function is used to return only positive values from the Adjustment values line item. This is done by using the number 0 and the Adjustment values line item with the MAX function.

The module also uses the MIN function to return only negative values.


Jan 22Feb 22Mar 22Apr 22May 22Jun 22
Adjustment values-1,2302,6371,829-3,1092,019320

Positive Adjustment values only

MAX(Adjustment values, 0)

02,6371,82902,019320

Negative Adjustment values only

MIN(Adjustment values, 0)

-1,23000-3,10900

In this example, a module is dimensioned by Time and line items. The first five line items contain the monthly salary costs for different departments. The final line item contains a formula that determines which department incurs the highest salary cost each month.


Jan 21Feb 21Mar 21
Sales124,254112,469132,525
Production64,63166,29872,049
Executive175,302169,030190,298
Finance97,21097,210102,051
HR71,03571,03571,035

Highest monthly departmental salary costs

MAX(Sales, Production, Executive, Finance, HR)

175,302169,030190,298

In this example, a module is dimensioned by the Products list and line items. The first four line items contain product release dates for different regions, which have the date data type. The final line item contains a formula that determines the most recent product release for each product among each region.


Monet dressRenoir dressPicasso topCezanne sweatpantsMatisse pumps
N America01/04/202101/04/202112/07/202112/07/202121/07/2021
S America07/05/202123/05/202119/07/202119/07/202121/07/2021
Europe27/03/202127/03/202112/03/202112/07/202128/07/2021
Asia Pacific12/06/202112/06/202112/06/202112/07/202128/07/2021

Most recent global release date

MAX(N America, S America, Europe, ASia Pacific)

12/06/202112/06/202119/07/202119/07/202128/07/2021