Search

x[MAX:y]

Returns the maximum value of a line item from a source module. The source and result module must both use the line item as a dimension: the source uses the dimension as a list-formatted line item.

Syntax

x[MAX: y] 

where:

  • x: line item to search for a maximum value
  • y: list item to use as criteria for the search

Format

Input Format Output Format

x: Line item

y: ListItem

Matches the data format used by argument x

Arguments

The function uses the following arguments:

  • x: number, date, or time period-formatted source line item.
  • y: list-formatted source line item.

Constraints

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.

Excel equivalent

  • No Excel equivalent

Back to Top

Example

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]        

Note that MAX does not return a value in the result for Los Angeles, York and Lyon—this is because there are no selections for these cities in the City source line item, that is formatted on the City Location list.

Back to Top

See also