1 of 8

Aggregation

Average

The Average() function calculates the average of a list of numbers, date times or duration.

This function is NOT SQL compatible. For more information about SQL compatibility, see our documentation.

Returns number, datetime or duration that is the average of the given values.

Returns Unknown if one of the parameter values is unknown or outside the common valid boundaries.

Syntax

Average(List(values) or [field] of [list])

Average(List(1, 3, 6, 2))=> Result: 3

Average([Products.Price] of [Products])=> Result: average Price-value of all products in the list [Products]

Parameters

Name

Type

Required

Description

Count

The Count() function returns the number of items in a list or concept set.

This function is SQL compatible. For more information about SQL compatibility, see our .

Returns the number of items in the given list or concept_set.

Returns Unknown if one of the parameter values is unknown or outside the common valid boundaries.

Syntax

Count(list)

Count(List("A", "B", "C"))=>Result: 3

Count([Products] where [Product.Price] > 10)=>Result: returns the number of products that cost more than 10 euro

Count()=>Result: unknown

Count(concept_set)

Count({ Color.Orange, Color.Blue, Color.Red })=>Result: 3

Count(unknownconceptset)=>Result: unknown

Parameters

Name

Type

Required

Description

First

The First() function returns the first item in a list or concept set.

This function is NOT SQL compatible. For more information about SQL compatibility, see our documentation.

Returns the first item in the given list or concept_set.

Returns Unknown if one of the parameter values is unknown or outside the common valid date boundaries.

Syntax

First(list)

First(List("A", "B", "C"))=>Result: "A"

First([Products] where [Product.Price] > 10)=>Result: returns the first row of products that cost more than 10 euro

First()=>Result: unknown

First(concept_set)

First({ Color.Orange, Color.Blue, Color.Red })=>Result: Color.Orange

First()=>Result: unknown

Parameters

Name

Type

Required

Description

Last

The Last() function returns the last item in a list or concept set.

For (persistent) lists it must indicate a field of that list.

This function is NOT SQL compatible. For more information about SQL compatibility, see our .

Returns the last item in the given list or concept_set.

Returns Unknown if one of the parameter values is unknown or outside the common valid date boundaries.

Possible fault message in expression editor: Invalid call to a function: parameter mismatch at position(x,y): Last

This could mean that there is no field of list used, but only the (reference to a) list.

The Last function needs a field from list where filter construction, or if you only want to get the last row of a list, use RowId([list]) of [list] where [filter].

Syntax

Last(List(values) or [field] of [list])

Last(List("A", "B", "C"))=>Result: "C"

Last(RowId([list]) of [list] where HasValue([field in list]))=>Result: last row in the indicated list where a certain field has a value

Parameters

Name

Type

Required

Description

Max

The Max() function calculates the highest value of a list of numbers, datetimes or durations.

This function is SQL compatible. For more information about SQL compatibility, see our documentation.

Returns a number, datetime or duration that is the highest value of the given values.

Returns Unknown if one of the parameter values is unknown or outside the common valid date boundaries.

Syntax

Max(List(values) or [field] of [list])

Max(List(1, 2, 5, 3))=> Result: 5

Max(List(45s, 12s, 50s, 1m25s)=> Result: 1m25s

Max([Products.Price] of [Products])=> Result: the max Price-value of all products in the list [Products]

Max()=> Result: unknown

Parameters

Name

Type

Required

Description

Min

The Min() function calculates the lowest value of a list of numbers, datetimes or durations.

This function is SQL compatible. For more information about SQL compatibility, see our documentation.

Returns a number, datetime or duration that is the lowest value of the given values.

Returns Unknown if one of the parameter values is unknown or outside the common valid date boundaries.

Syntax

Min(List(values) or [field] of [list])

Min(List(1, 2, 5, 3))=>Result: 1

Min(List(45s, 12s, 50s, 1m25s)=>Result: 12s

Min([Products.Price] of [Products])=> Result: the min Price-value of all products in the list [Products]

Min()=>Result: unknown

Parameters

Name

Type

Required

Description

Sum

The Sum() function calculates the total value of a list of numbers or durations.

This function is SQL compatible. For more information about SQL compatibility, see our documentation.

Returns a number or duration that is the total value of the given values.

Returns Unknown if one of the parameter values is unknown or outside the common valid date boundaries.

Syntax

Sum(List(values) or [field] of [list])

Sum(List(1, 2, 5))=>Result: 8

Sum([Products.Price] of [Products])=> Result: the summation of all Price-value of all products in the list [Products]

Sum()=>Result: unknown

Parameters

Name

Type

Required

Description