This article lists Aggregate Functions that can be used within Model Calculation expressions.
Aggregation is an operation that combines values from multiple records into a single value.
Within Model Calculation expressions, Aggregate Functions can currently only be used on child records. It is also possible to perform aggregation on “grandchild” and other Groups lower in the database hierarchy.
Please see the Aggregation in Model Calculations guide for more information and examples.
For additional information on other functions and expressions in general, please see our Functions Guide and Expressions Guide.
Aggregation is performed slightly differently within Template Studio expressions. Please see the Aggregation in Templates guide for more information. Also note that the functions listed here only apply to Model Calculation expressions. A slightly different list of Aggregate Functions are available for Template expressions.
Table of Contents
Average()
Calculates the average of the values.
Average([ChildGroup.NumericHeader])Count()
Counts the number of records where the specified Header contains data.
Count([ChildGroup.Header])CountRecords()
Counts the number of records for the Group whether the specified Header contains data or not.
CountRecords([ChildGroup.Header])Filter()
Filters the child records within an Aggregate expression based on a filter condition.
Filter() must always be used within some other Aggregate function. The Aggregate operation is then performed on the sub-set of child records returned by the Filter() function.
Filter([ChildGroup.Header], Filter Condition)Currently, it is only possible to write filter conditions using the “=” operator. In the future we plan to support other operators (such as >, <, and/or, etc.).
The two arguments in the Filter() function must reference the same Group. In general, the filter condition would compare some Header in that Group against a single value (such as a numeric value, string literal, nested expression, or Header from a parent Group). When saved, validations will be performed on the filter condition.
Example
In this example, Filter() will be used to determine the minimum and maximum Water Levels in the MonitoringReadings Group. To do this, we’ll add calculated headers in the parent MonitoringInstallationsAndInstruments Group. In the example data, there are ‘Reading Types’ for Temperature (TEMP), pH, and Water Levels (WDEP), so Filter() is used to find the minimum and maximum for just the WDEP readings:
MinAll(Filter([MonitoringReadings.Reading],[MonitoringReadings.ReadingType]='WDEP'))MaxAll(Filter([MonitoringReadings.Reading],[MonitoringReadings.ReadingType]='WDEP'))Looking at the results, we see a minimum Water Level of 9.00 m and a maximum of 9.90 m was recorded in this example.
Note – the calculations above were performed in the parent MonitoringInstallationsAndInstruments Group so the results are per instrument in that Location (the example data above happens to only have 1 instrument). Alternatively, because it is possible to aggregate grandchild data, the calculation could have been performed in the LocationDetails Group, which would have returned the minimum and maximum values across all instruments in that Location (had there been multiple).
First()
Determines the first value from a list of values. As described in the Aggregation in Model Calculations guide, records are ordered by Key Headers.
First([ChildGroup.Header])Interpolate()
Performs linear interpolation on a dataset to return a value for a given “lookup value”.
The function computes the return value by performing piecewise linear interpolation between the two points within the dataset that fall before and after the lookup value.
As described in the Aggregation in Model Calculations guide, records are ordered by Key Headers when performing the interpolation.
The function takes two forms – with or without optional ScaleOptions, which can be used to specify arithmetic or logarithmic scales.
Interpolate(LookupValues, ReturnValues, LookupValue)Interpolate(LookupValues, ReturnValues, LookupValue, LookupValueScaleOption, ReturnValueScaleOption)- The LookupValues and ReturnValues must reference a [ChildGroup.NumericHeader].
- LookupValues, ReturnValues and LookupValue must be numeric data types.
- LookupValues and ReturnValues must be from the same Group.
- The ScaleOptions can be null for an arithmetic scale or a positive numeric value (not equal to 1) to indicate the base of a logarithmic scale. For example, a ScaleOption of 10 would indicate a logarithmic base 10 scale.
- The ScaleOptions are optional. If both are omitted, arithmetic scales are used. It’s not allowed to include just one of the ScaleOptions. Both must be omitted or included (either 3 or 5 arguments must be provided).
When performing the calculation, the following validations are performed:
- There must be an equal number of LookupValues and ReturnValues after discarding records with null values.
- There must be at least two valid records. If not, the function will return null.
- If the LookupValue is outside the range of LookupValues, then the function returns null.
- The LookupValues in the dataset must all be continuously increasing or decreasing. For example, the dataset at right below is invalid because there could be multiple ReturnValues for certain LookupValues. The Interpolate() function will always return null for such datasets.
Example
Interpolate() can be used to calculate D60 for a Particle Size Distribution test (particle size at which 60% of the soil particles are smaller) using the expression below in the ParticleSizeDistributionGeneral Group. The LookupValues (PercentagePassing) use an arithmetic scale (null) and the ReturnValues (ParticleSize) use a logarithmic scale (10).
Interpolate(
[ParticleSizeDistributionData.PercentagePassing],
[ParticleSizeDistributionData.ParticleSize],
60,
null,
10
)InterpolateBFLinear()
Returns a value for a given "lookup value" along a linear best fit line through a dataset.
The function takes two forms – with or without optional ScaleOptions which can be used to specify arithmetic or logarithmic scales.
InterpolateBFLinear(LookupValues, ReturnValues, LookupValue) InterpolateBFLinear(LookupValues, ReturnValues, LookupValue, LookupValueScaleOption, ReturnValueScaleOption) - The LookupValues and ReturnValues must reference a [ChildGroup.NumericHeader].
- LookupValues, ReturnValues and LookupValue must be numeric data types.
- LookupValues and ReturnValues must be from the same Group.
- The ScaleOptions can be null for an arithmetic scale or a positive numeric value (not equal to 1) to indicate the base of a logarithmic scale. For example, a ScaleOption of 10 would indicate a logarithmic base 10 scale.
- The ScaleOptions are optional. If both are omitted, arithmetic scales are used. It’s not allowed to include just one of the ScaleOptions. Both must be omitted or included (either 3 or 5 arguments must be provided).
When performing the calculation, the following validations are performed:
- There must be an equal number of LookupValues and ReturnValues after discarding records with null values.
- There must be at least two valid records. If not, the function will return null.
- If one of the LookupValues or ReturnValues is null, that row of data will not be considered when generating the best fit line.
- If the LookupValue is outside the range of LookupValues, then the function returns null.
Example
In this example, InterpolateBFLinear() will be used to determine a "representative" N-Value (ReturnValue) at 3 m depth (LookupValue) using the expression below. The ScaleOptions are omitted, in which case arithmetic scales are used. (This example is intended for illustrative purposes of the function usage rather than a real world example.)
InterpolateBFLinear(
[SPT.DepthTop],
[SPT.NValue],
3
)Looking at the results, we see a value has been computed for all Locations except BH3 where the LookupValue (3) is outside the range of the LookupValues (DepthTop).
Last()
Determines the last value from a list of values.
As described in the Aggregation in Model Calculations guide, records are ordered by Key Headers.
Last([ChildGroup.Header])MaxAggregate()
MaxAggregate() is an alias for MaxAll(). (It returns the same results as MaxAll(). MaxAggregate() can just be used as the function name instead.)
MaxAll()
Determines the maximum value from a list of values.
(Note there is also a max() function, which is not an Aggregate Function.)
MaxAll([ChildGroup.NumericHeader])MinAggregate()
MinAggregate() is an alias for MinAll(). (It returns the same results as MinAll(). MinAggregate() can just be used as the function name instead.)
MinAll()
Determines the minimum value from a list of values.
(Note there is also a min() function, which is not an Aggregate Function.)
MinAll([ChildGroup.NumericHeader])