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 [, testField [, testValue [, testOperator]]] )

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.
  • testField
    • Optional. When specified will filter or limit the concatenation to values where the testField field satisfies the condition from the testValue and testOperator arguments.
  • testValue
    • Optional. Used in conjunction with testField. The value to compare to.
  • testOperator
    • Optional. Used in conjunction with testField. The operator used to compare the value of the testField with the supplied testValue.

    • Operation

    Symbolic Value

    Non-Symbolic Value

    Integer Value

    Equls

    =

    eq

    1

    Not Equals

    <>

    new

    2

    Less Than

    <

    lt

    3

    Less Than or Equals

    <=

    lte

    4

    Greater Than

    >

    gt

    5

    Greater Than or Equals

    >=

    gte

    6

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

To concatenate the description field on the orderLines transaction where the qty field is equal to zero (0).

Concatenate("description", "orderLines", ",", "qty", 0)

To concatenate the description field on the orderLines transaction where reference field is not empty.

Concatenate("description", "orderLines", ",", "reference", "", "<>")

To concatenate the description field on the orderLines transaction where the description itself not empty.

Concatenate("description", "orderLines", ",", "description", "", "neq")

Back to top

Count

Description

Returns the count of records in current or sibling transaction.

Syntax

  • Count(transaction [,field [, testField [, testValue [, testOperator]]] )

Arguments

  • transaction
    • Must be either the transaction type of the current record or one of its siblings.
  • field
    • Optional. Used to specify the field to 'count' when counting aggregate fields. This is not used and can omitted when counting child transactions.
  • testField
    • Optional. When specified will filter or limit the count to values where the testField field satisfies the condition from the testValue and testOperator arguments.
  • testValue
    • Optional. Used in conjunction with testField. The value to compare to.
  • testOperator
    • Optional. Used in conjunction with testField. The operator used to compare the value of the testField with the supplied testValue.

  • Operation

    Symbolic Value

    Non-Symbolic Value

    Integer Value

    Equls

    =

    eq

    1

    Not Equals

    <>

    new

    2

    Less Than

    <

    lt

    3

    Less Than or Equals

    <=

    lte

    4

    Greater Than

    >

    gt

    5

    Greater Than or Equals

    >=

    gte

    6

Samples

To count the number of records within a child transaction.

Count( "transaction" )

To count the qty field within an aggregate transaction.

Count(".", "qty")

To count the orderLines transaction where the qty field is equal to zero (0). Note the second argument is an empty string - it must be supplied but is unused.

Count("orderLines", "", "qty", 0)

To count the the orderLines transaction where reference field is not empty.

Count("orderLines", "", "reference", "", "<>")

To count the orderLines transaction where the description itself not empty.

Count("orderLines", "", "description", "", "neq")

To count the reference aggregate field where the reference field not empty.

Count(".", "reference", "reference", "", "neq")

Back to top

DistinctCount

Description

Returns the distinct number of values in current or sibling transaction.

Syntax

  • DistinctCount(field, transaction [, testField [, testValue [, testOperator]]] )

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.
  • testField
    • Optional. When specified will filter or limit the distinct count to values where the testField field satisfies the condition from the testValue and testOperator arguments.
  • testValue
    • Optional. Used in conjunction with testField. The value to compare to.
  • testOperator
    • Optional. Used in conjunction with testField. The operator used to compare the value of the testField with the supplied testValue.

  • Operation

    Symbolic Value

    Non-Symbolic Value

    Integer Value

    Equls

    =

    eq

    1

    Not Equals

    <>

    new

    2

    Less Than

    <

    lt

    3

    Less Than or Equals

    <=

    lte

    4

    Greater Than

    >

    gt

    5

    Greater Than or Equals

    >=

    gte

    6

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

To find the distinct count of the description field on the orderLines transaction where the qty field is equal to zero (0).

DistinctCount("description", "orderLines", "qty", 0)

To find the distinct count where the description field on the orderLines transaction where reference field is not empty.

DistinctCount("orderLines", "", "reference", "", "<>")

To find the distinct count of the description field on the orderLines transaction where the description itself not empty.

DistinctCount("orderLines", "", "description", "", "neq")

To find the distinct count the reference aggregate field where the reference field not empty.

DistinctCount(".", "reference", "reference", "", "neq")

Back to top
 

First

Description

Returns the first value from either:

  • Array
    • A value from an array of values.
  • Transaction
    • A value in current or sibling transaction.

values in current or sibling transaction.

Syntax

  • First( transactionOrArray, [, field [, testField [, testValue [, testOperator]]]] )

Arguments

  • transactionOrArray
    • Either the child transaction id or the array to obtain the first element.
    • Where an array is supplied, the field and testField, testValue and testOperation arguments are ignored.
  • field
    • The field of the child transaction.
    • To query an aggregate field specify "." otherwise specify a valid transaction type.
  • testField
    • Optional. When specified will filter the first to return the first value where the testField field satisfies the condition from the testValue and testOperator arguments.
  • testValue
    • Optional. Used in conjunction with testField. The value to compare to.
  • testOperator
    • Optional. Used in conjunction with testField. The operator used to compare the value of the testField with the supplied testValue.

    • Operation

    Symbolic Value

    Non-Symbolic Value

    Integer Value

    Equls

    =

    eq

    1

    Not Equals

    <>

    new

    2

    Less Than

    <

    lt

    3

    Less Than or Equals

    <=

    lte

    4

    Greater Than

    >

    gt

    5

    Greater Than or Equals

    >=

    gte

    6

Samples

Returns 'first' being the first element of an array argument.

First (Array("first", "second", "third)) 

To returns first element from the result of the Split function.

Dim Vals

Vals = Split(str, ",")

First (Vals) 

Returns the description from the first record of the 'lines' transaction.

First("lines", "description")

Returns the first value of the qty aggregate field.

First(".", "qty")

To get the first record's description field on the orderLines transaction where the qty field is equal to zero (0).

First("description", "orderLines", "qty", 0)

Returns the first record's description field on the orderLines transaction where reference field is not empty.

Concatenate("description", "orderLines", "reference", "", "<>")

Returns the first record's description field on the orderLines transaction where the description itself not empty.

Concatenate("description", "orderLines", "description", "", "neq")

Back to top
 

IsIn

Description

Returns true/false to indicate if a supplied value exists in either:

  • Array
    • A value from an array of values.
  • Transaction
    • A value in current or sibling transaction.

values in current or sibling transaction.

Syntax

  • Last(testValue, transactionOrArray, [, field] )

Arguments

  • testValue
    • The value to compare to.
  • transactionOrArray
    • Either the child transaction id or the array to find the supplied value.
  • field
    • Optional. The field of the child transaction. This field is ignored when an array argument is supplied, but mandatory when a transaction Id is supplied to the function.
    • To query an aggregate field specify "." otherwise specify a valid transaction type.

Samples

Returns true - 2 is a value in the supplied array.

IsIn (2, Array(0, 1, 2, 2, 9)) 

Returns true - 2 is a value after the string has been split.

Dim Vals

Dim str

str = "9,two, 3, three, zero, 0, 2"

Vals = Split(str, ",")

IsIn (Vals) 

Indicates if any qty field in the lines transaction has a zero (0) value.

IsIn (0, "lines", "qty")

Indicates if any value of the qty aggregate field is zero (0).

IsIn (0, ".", "qty")

Back to top
 

Last

Description

Returns the last value from either:

  • Array
    • A value from an array of values.
  • Transaction
    • A value in current or sibling transaction.

values in current or sibling transaction.

Syntax

  • Last( transactionOrArray, [, field [, testField [, testValue [, testOperator]]]] )

Arguments

  • transactionOrArray
    • Either the child transaction id or the array to obtain the last element.
    • Where an array is supplied, the field and testField, testValue and testOperation arguments are ignored.
  • field
    • The field of the child transaction.
    • To query an aggregate field specify "." otherwise specify a valid transaction type.
  • testField
    • Optional. When specified will filter the last to return the last value where the testField field satisfies the condition from the testValue and testOperator arguments.
  • testValue
    • Optional. Used in conjunction with testField. The value to compare to.
  • testOperator
    • Optional. Used in conjunction with testField. The operator used to compare the value of the testField with the supplied testValue.

    • Operation

    Symbolic Value

    Non-Symbolic Value

    Integer Value

    Equls

    =

    eq

    1

    Not Equals

    <>

    new

    2

    Less Than

    <

    lt

    3

    Less Than or Equals

    <=

    lte

    4

    Greater Than

    >

    gt

    5

    Greater Than or Equals

    >=

    gte

    6

Samples

Returns 'third' being the first element of an array argument.

Last (Array("first", "second", "third)) 

To returns last element from the result of the Split function.

Dim Vals

Vals = Split(str, ",")

Last (Vals) 

Returns the description from the last record of the 'lines' transaction.

Last ("lines", "description")

Returns the last value of the qty aggregate field.

Last (".", "qty")

To get the last record's description field on the orderLines transaction where the qty field is equal to zero (0).

Last ("description", "orderLines", "qty", 0)

Returns the last record's description field on the orderLines transaction where reference field is not empty.

Last ("description", "orderLines", "reference", "", "<>")

Returns the last record's description field on the orderLines transaction where the description itself not empty.

Last ("description", "orderLines", "description", "", "neq")

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 [, testField [, testValue [, testOperator]]] )

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.
  • testField
    • Optional. When specified will filter or limit the minimum operation to values where the testField field satisfies the condition from the testValue and testOperator arguments.
  • testValue
    • Optional. Used in conjunction with testField. The value to compare to.
  • testOperator
    • Optional. Used in conjunction with testField. The operator used to compare the value of the testField with the supplied testValue.

  • Operation

    Symbolic Value

    Non-Symbolic Value

    Integer Value

    Equls

    =

    eq

    1

    Not Equals

    <>

    new

    2

    Less Than

    <

    lt

    3

    Less Than or Equals

    <=

    lte

    4

    Greater Than

    >

    gt

    5

    Greater Than or Equals

    >=

    gte

    6

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

To find the minimum of the qty field on the orderLines transaction where the item field is equal to "ABC-001".

Minimum ("qty", "orderLines", "item", "ABC-001")

To find the minimum of the unitPrice field on the orderLines transaction where the item field is not equal to "SHIPPING".

Minimum ("unitPrice", "orderLines", "item", "SHIPPING", "<>")

To find the minimum of the extendedAmount aggregate field where the qty is greater than 1.

Minimum (".", "extendedAmount", "qty", 1, ">")

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 [, testField [, testValue [, testOperator]]] )

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.
  • testField
    • Optional. When specified will filter or limit the maximum operation to values where the testField field satisfies the condition from the testValue and testOperator arguments.
  • testValue
    • Optional. Used in conjunction with testField. The value to compare to.
  • testOperator
    • Optional. Used in conjunction with testField. The operator used to compare the value of the testField with the supplied testValue.

  • Operation

    Symbolic Value

    Non-Symbolic Value

    Integer Value

    Equls

    =

    eq

    1

    Not Equals

    <>

    new

    2

    Less Than

    <

    lt

    3

    Less Than or Equals

    <=

    lte

    4

    Greater Than

    >

    gt

    5

    Greater Than or Equals

    >=

    gte

    6

Samples

To find the minimum a field within a child transaction.

Maximum ("field", "transaction")

To find the maximum of a field within an aggregate transaction.

Maximum ("field", ".")

To find the maximum of the qty field on the orderLines transaction where the item field is equal to "ABC-001".

Maximum ("qty", "orderLines", "item", "ABC-001")

To find the maximum of the unitPrice field on the orderLines transaction where the item field is not equal to "SHIPPING".

Maximum("unitPrice", "orderLines", "item", "SHIPPING", "<>")

To find the maximum of the extendedAmount aggregate field where the qty is greater than 0.

Maximum(".", "extendedAmount", "qty", 0, ">")

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 [, testField [, testValue [, testOperator]]] )

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.
  • testField
    • Optional. When specified will filter or limit the sum operation to values where the testField field satisfies the condition from the testValue and testOperator arguments.
  • testValue
    • Optional. Used in conjunction with testField. The value to compare to.
  • testOperator
    • Optional. Used in conjunction with testField. The operator used to compare the value of the testField with the supplied testValue.

  • Operation

    Symbolic Value

    Non-Symbolic Value

    Integer Value

    Equls

    =

    eq

    1

    Not Equals

    <>

    new

    2

    Less Than

    <

    lt

    3

    Less Than or Equals

    <=

    lte

    4

    Greater Than

    >

    gt

    5

    Greater Than or Equals

    >=

    gte

    6

Samples

To find the sum of the LINETOTAL field from OrderDetail transaction:

Sum ("LINETOTAL","OrderDetail")

To sum the extendedAmount aggregate field.

Sum ("extendedAmount", ".")

To find the sum of the qty field on the orderLines transaction where the item field is equal to "SHIPPING".

Sum ("extendedAmount", "orderLines", "item", "SHIPPING")

To find the sum of the qty field on the orderLines transaction where the item field is not equal to "SHIPPING".

Sum ("qty", "orderLines", "item", "SHIPPING", "<>")

To find the sum of the extendedAmount aggregate field where the qty is greater than 0.

Sum (".", "extendedAmount", "qty", 0, ">")