Function list

We use an advanced expression engine to process calculation expressions. This engine supports many functions and filters. Read all about this in the documentation of the advanced calculations.

On top of the engine's standard features, we added a number of extra functions to support your Math processing. These functions can be found in the Function dropdown in AlisQI.
The lists below define the available functions.

Numeric functions

  • ABS( value ) absolute value
  • AVERAGE( value, value, ..., value) average of the non-empty values
  • MOVINGAVG( field, number ) moving average
  • DELTA( field ) difference compared to previous result
  • LOG( value, [base] ) log (base 10 by default)
  • POWER( base, power ) base raised to the power
  • MIN( value, value, ..., value ) minimum of non-empty values
  • MAX( value, value, ..., value ) maximum of non-empty values
  • STDEV( value, value, ..., value ) standard deviation of non-empty values
  • RANGE( value, value, ..., value ) Range (max-min) for given fields or values
  • CEIL( value ) Round fractions up (1.2 will be rounded to 2)
  • FLOOR( value ) Round fractions down (1.9 will be rounded to 1)

field = Field name of a numeric field (numeric or calculation)
value = Field name of a numeric field or an expression

Date functions

Subtracting dates is possible using these two functions:

  • DATEDIFF_MIN( dateField, dateField) Difference in minutes between date1 and date2
  • DATEDIFF_DAY( dateField, dateField) Difference in days between date1 and date2

To calculate a date based on a reference date and offset, you can use:

  • DATE_ADD_DAY( dateField, offset) offset can be a static value, or reference to a field
  • DATE_ADD_MONTH( dateField, offset) offset can be a static value, or reference to a field

This will result in a new date (or datetime).

Text functions

Text calculation fields can use the following functions:
  • CONCAT ( value, value, value, ...) This function glues all values together. Values can either be fieldnames or static text strings.
    CONCAT({code}, ' - ', {name}) with code = 123 and name = ABC will result in "123 - ABC"

Expressions & nesting

Value parameters can either be field names or expressions. This allows for function-nesting, where the output of a function is used as input for another function.

The DELTA and MOVINGAVG functions only accept a fieldname as parameter, and no expressions.
This is due to the fact that these functions evaluate multiple results (rows in the results overview).

Read all about the advanced possibilities of calculations in AlisQI

Still need help? Contact Us Contact Us