Setting an aggregate function for a table column allows you to get a single value on the upper grouping level by performing a certain operation on the values of the lower grouping levels.

For example, you can set up a table that displays Available inventory with additional details on each facility Type and offered Product:

If you group the table data by Product, the upper level of the table will contain one grouping entry for each Product. The lower level of every grouping entry will contain the entries for each available Type. Note that the lower level entries display the Available inventory values in the Value column, but the upper level entries do not:

Setting an aggregate function for the Value column will allow you to view the Available inventory values on the upper grouping level.

To set an aggregate function for a column

1. Click the Group data icon to enter the grouping mode. The table header will acquire a new line with grouping checkboxes and aggregate functions drop-downs (depending on the value type of a column).

2. Right-click the No aggregation option below the Value column time to open the drop-down list of aggregation functions.

   The following aggregation functions are available:

   • No aggregation — [selected by default] pick this option if no aggregate function is required.
   • Count — returns the number of the grouped values.
   • Mean — returns the mean (arithmetic average) of the grouped values.
   • Sum — returns the sum of the grouped values.
   • Min — returns the minimum value of the grouped data.
   • Max — returns the maximum value of the grouped data.

   

3. Let us select the Sum aggregation function. In this case we will get the value of Available inventory that is calculated for each Product as the sum of the individual Available inventory values for each Type:

   The bottom of the table will show the AGGREGATED VALUES line, which contains the final aggregated value of the selected aggregation function(s). In case of Sum it shows the total amount for all table records. In case of Mean it shows the overall mean value for all table records. In case of Count it shows the number of grouped table records. In case of Min and Max functions it shows the smallest and the largest values respectively.

   

If you set the Mean aggregation function for the Value column, you will get the Available inventory value calculated for each Product as the average of individual Available inventory values for each Type.

How can we improve this article?

Send