Aggregate Functions
Aggregate functions serve a dual purpose. They can query records in sibling or the current transaction types and they can query the values contained in a field if the field has been set to allow value accumulation in an aggregate transform.
Concatenate
Description
Returns a string which is the concatenation of the values of an aggregate field or of a field in the current or sibling transaction.
Syntax
- Concatenate(field,transaction,separator)
Arguments
- field
- The field from which the minimum is to be obtained.
- transaction
- The transaction to be queried.
- To query an aggregate field specify “.” otherwise specify a valid transaction type.
Samples
To concatenate a field within a child transaction where each value is separated by a space, dash, space.
Concatenate(“field”, “transaction”, “ – “)
To concatenate a field within an aggregate transaction where each value is separated by a comma.
Concatenate(“field”, “.”, “,“)
Count
Description
Returns the count of records in current or sibling transaction.
Syntax
- Count(transaction)
Arguments
- Transaction
- Must be either the transaction type of the current record or one of its siblings.
Samples
To count the number of records within a child transaction.
Count( “transaction” )
DistinctCount
Description
Returns the distinct number of values in current or sibling transaction.
Syntax
- DistinctCount(field, transaction)
Arguments
- field
- The field for which the distinct count is maintained.
- Transaction
- Must be either the transaction type of the current record or one of its siblings.
- To query an aggregate field specify “.” otherwise specify a valid transaction type.
Samples
To find the distinct count a field within a child transaction.
DistinctCount (“field”, “transaction”)
To find the distinct count of a field within an aggregate transaction.
DistinctCount (“field”, “.”)
Minimum
Description
Returns the minimum value of an aggregate field or of a field in the current or sibling transaction.
Syntax
- Minimum(field, transaction)
Arguments
- field
- The field from which the minimum is to be obtained.
- transaction
- The transaction to be queried.
- To query the aggregate field specify “.” otherwise specify a valid transaction type.
Samples
To find the minimum a field within a child transaction.
Minimum (“field”, “transaction”)
To find the minimum of a field within an aggregate transaction.
Minimum (“field”, “.”)
Maximum
Description
Returns the maximum value of an aggregate field or of a field in the current or sibling transaction.
Syntax
- Maximum(field, transaction)
Arguments
- field
- The field from which the maximum is to be obtained.
- transaction
- The transaction to be queried.
- To query the aggregate field specify “.” otherwise specify a valid transaction type.
Sum
Description
Returns the sum of the values within an aggregate field or of a field in the current or sibling transaction.
Syntax
- Sum(field, transaction)
Arguments
- field
- The field from which the maximum is to be obtained.
- transaction
- The transaction to be queried.
- To query the aggregate field specify “.” otherwise specify a valid transaction type.
Samples
To find the sum of the LINETOTAL field from OrderDetail transaction:
SUM("LINETOTAL","OrderDetail")