Aggregate Functions

Owned by Enoch Poston
Last updated: Aug 06, 2024 by Kim Dyer
3 min read

Aggregate functions calculate values based on two factors: the input cell, which is usually, but not always a data field, and the report section that the function is located, relative to the input. The location of the function determines which, and how many cell values will be sent to the input. For example, an AggCount() function in a Group Footer will count the values in the group, but that same function in a Report Footer will count the values for the entire report.

Note: Aggregate functions cannot be used in conditional formulas or ExpressView formula columns.

Note: Aggregate functions are not capable of summarizing data within static sections of a report, such as the page and report headers and footers. For more information, please see the article on static sections.

Advanced Users - Aggregating for One-to-Many Relationships

As of v2016.3.8, aggregate functions have an optional argument that indicates how to count aggregates for one-to-many relationships. This argument indicates whether aggregation should occur at the record level or entity level. Pass True() to force aggregation to occur for every record, regardless of whether or not the record represents a unique instance of the entity that is being aggregated. By default, aggregation occurs only for every unique instance of the entity being aggregated, not for repeat values that occur from a one-to-many data relationship.

For example, suppose two data objects are being joined on a field that is not unique for each record (e.g., transaction account number). If a transaction account number exists that appears twice or more in succession and the optional second argument for the aggregate function (e.g., AggSum()) is set to False() or left at its default value, then the subsequent rows will not be included in the aggregation because they are interpreted as duplicates of the unique entity. However, if the second argument of AggSum() is set to True(), then every record, including all duplicates, will be included in the calculation. 

Most users can choose to ignore this option by simply omitting the argument.

 

 

AggAvg

Description

Returns the average of the values in a group.

Remark

Accepts data fields or cell references.

Optional second argument (v2016.3.8+) indicates whether to count: 

  • Records: True

  • Entites: False (default)

Example

AggAvg({OrderDetail.Quantity}) – returns the average quantity of sales orders.

 

AggCount

Description

Returns the number of unique entities in a Data Category.

Remark

The AggCount() function uses the Data Category, not the Data Field. For example, the function AggCount({Officer.Salary}) counts the number of Officers. You could replace Officer.Salary with any other field in the Officer Data Category and the function would still count the number of officers.

Optional second argument (v2016.3.8+) indicates whether to count: 

  • Records: True

  • Entites: False (default)

Example

AggCount({Orders.ProductPrice}) – returns the number of sales orders.

 

AggDistinctCount

Description

Returns the number of unique values in a group.

Remark

Accepts data fields or cell references.

Optional second argument (v2016.3.8+) indicates whether to count: 

  • Records: True

  • Entites: False (default)

Optional third argument (v2018.1.8+) indicates whether to count null as a distinct value.

  • Count: True

  • Ignore: False (default)

Example

AggDistinctCount({OrderDetail.Quantity}) – returns the number distinct quantities in an order.

 

AggMax

Description

Returns the maximum value in a group.

Remark

Accepts data fields or cell references.

Optional second argument (v2016.3.8+) indicates whether to count: 

  • Records: True

  • Entites: False (default)

Example

AggMax({OrderDetail.Discount}) – returns the largest discount.

 

AggMin

Description

Returns the minimum value in a group.

Remark

Accepts data fields or cell references.

Optional second argument (v2016.3.8+) indicates whether to count: 

  • Records: True

  • Entites: False (default)

Example

AggMin({OrderDetail.Discount}) – returns the smallest discount.

 

AggSum

Description

Returns the sum of the values in a group.

Remark

Accepts data fields or cell references.

Optional second argument (v2016.3.8+) indicates whether to count: 

  • Records: True

  • Entites: False (default)

Example

AggSum({OrderDetail.Quantity}) – returns the total quantity of units ordered.

 

 

RunningSum

Description

Returns a running total of the input cell.

Remark

Takes one, two, or three input:

The cell you want to sum.

Optional second argument of a Data Field or Category. The running sum will reset to 0 whenever there is a new value for this Data Field or Category.

Optional third argument (v2016.3.8+) indicates whether to count: 

  • Records: True

  • Entites: False (default)

Examples

1. RunningSum({Employees.Salary}) – returns running total of all the employee’s salary.

2. RunningSum({Employees.Salary}, {Employees.Region}) – returns a running total of employee’s salary for each region.

3. RunningSum({Employees.Salary}, {Company}) – returns a running total of employee’s salary for each Company.

 

 

AggMedian

(v2017.2+)

Description

Returns the median, or the middle value, of a data set.

Remark

Accepts data fields or cell references.

Optional second argument (v2016.3.8+) indicates whether to count: 

  • Records: True

  • Entites: False (default)

Example

AggMedian({OrderDetail.Quantity}) - returns the median order quantity.

 

 

AggMode

(v2017.2+)

Description

Returns the mode, or the value that appears most often, of a data set.

Remark

Accepts data fields or cell references.

Optional second argument (v2016.3.8+) indicates whether to count: 

  • Records: True

  • Entites: False (default)

Example

AggMode({OrderDetail.Quantity}) – returns the mode of order quantity.

 

 

AggStandardDeviation

(v2017.2+)

Description

Returns the standard deviation (SD) of a data set. SD is used to quantify the spread of the values in a data set. A lower SD indicates that values are close to the mean, and a higher SD indicates that values are more spread.

Remark

Accepts data fields or cell references.

Takes one, two, or three input:

The cell to get the standard deviation

Optional second argument indicates whether to calculate:

  • Sample-based standard deviation: "String"

  • Population-based standard deviation: "Population"

Optional third argument (v2016.3.8+) indicates whether to count: 

  • Records: True

  • Entites: False (default)

Example

AggStandardDeviation({OrderDetail.Quantity}) – returns the standard deviation of order quantities.

 

 

AggVariance

(v2017.2+)

Description

Returns the variance of a data set. Variance is used to quantify the spread of the values in a data set, and is equal to the square of the standard deviation. Variance weighs outliers more heavily than standard deviation.

Remark

Accepts data fields or cell references.

Takes one, two, or three input:

The cell to get the variance

Optional second argument indicates whether to calculate:

  • Sample-based standard deviation: "String"

  • Population-based standard deviation: "Population"

Optional third argument (v2016.3.8+) indicates whether to count: 

  • Records: True

  • Entites: False (default)

Example

AggVariance({OrderDetail.Quantity}) – returns the variance of order quantities.

Â