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.

Back to top

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”, “.”, “,“)

Back to top

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” )

Back to top

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”, “.”)

Back to top

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”, “.”)

Back to top

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.
Back to top

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")