argMax and argMin functions

The argMax and argMin functions return the number of the row which contains the maximum or minimum value of the selected attribute.

If the maximum or minimum value is repeated more than once in the column, the function retrieves only the first row number where the maximum or minimum value is found.


Parameters

argMax(column, group)

Parameter

Description

column

The attribute you want to use in the function. Multiple attributes can be specified, as long as they are enclosed in double brackets, i.e. column((column1, column2)). The column parameter is mandatory.

group

The attribute by which you want to further group results.


Example - argMax(column)

The following example uses the Bike sales dataset.

Description

Screenshot

In the example, we want to retrieve the row ID of the maximum value of the Order_Quantity attribute.

Type the formula argMax($"Order_Quantity") and the attribute will be filled with the row ID of the highest value, which is 1992.

To verify it, go to row 1992.

To go to a specific row, right-click on any cell, and select Go to row. Then, type the row number and click Apply.

The highest value of the Order_Quantity attribute is 32.

We can double check that the maximum value of the Order_Quantity attribute is 32 by selecting the column and visualizing the basic statistics in the Info panel at the bottom of the screen.

If we want to group our results by a certain attribute values, we can specify a group parameter.

In the example here, we want our results to be grouped by the Year attribute.

The formula will be: argMax($"Order_Quantity",$"Year") and the attribute will be filled with the row ID of the highest Order Quantity value for each value of the Year attribute.

Then, group the results by the Year attribute, in case you need an overview of the argMax for each year. This is an optional operation.

The results are:

  • For 2013, the maximum value of the Order_Quantity attribute is in row 193;

  • For 2014, the maximum value of the Order_Quantity attribute is in row 91;

  • For 2015, the maximum value of the Order_Quantity attribute is in row 3214;

  • For 2016, the maximum value of the Order_Quantity attribute is in row 1992.

To verify it, go to the specific rows by clicking on any cell and selecting Go to row. Then, type the row number and click Apply.

We want to verify the maximum value for 2013, so we need to go to row 193.

The maximum Order_Quantity value for 2013 is 30.

As you can see in this screenshot, the value 30 is contained also in row 194, but the function returns only the first row ID which contains the maximum of the chosen attribute.


Example - argMin(column)

The following example uses the Bike Sales dataset.

Description

Screenshot

Now, we want to retrieve the row ID of the lowest value of the Order_Quantity attribute.

Type the formula: argMin($"Order_Quantity") and the attribute will be filled with the first row ID of the lowest value, that is 18.

To verify it, go to row 18.

To go to a specific row, right-click on any cell, row number, column header in the Data Manager and select Go to row. Then, type the row number and click Apply.

The lowest value of the Order_quantity attribute is 1.

As you can see in this screenshot, the value 1 is contained also in row 19, but the function returns only the first row ID which contains the minimum of the chosen attribute.