Formula reference

General syntax for writing custom field formulas:

Fn.name(argument[, argument...])

Function argument can be another function which return required data type, or expression. Expressions must be placed inside curly brackets and can contain python code including contants, arithmetic operations and API calls. All arithmetics must be composed using the available functions.

Existing field value can be referenced using the row variable using field alias:

row['alias']

Full example of a formula for calculating change percentage based on the canonical equation (a-b)/b:

Fn.div(Fn.minus(row['a'],row['b']),row['b'])

Full example of a formula for calculating the daily average of an aggregated column:

Fn.div(row['some_sum'],{context.API.range(context.start,context.end,unit='days')|length}

Dynamic variables are are passed to the function interpreter via the context variable. the context object has the following properties:

• context.start - report start date

• context.end - report end date

• context.API - additional builtin helper functions providing facilities for manipulating other values such as time shifts, or subset generation, for example. Refer the context API documentation for available functions. 

• context.config - access to site configuration values. 

The following functions are available in custom field (function name, arguments and return type):

   Fn.if(a any,b any,c any) -> any b|c

   Fn.mult(a float|int, b float|int) -> a*b = float|int

   Fn.minus(a float|int, b float|int) -> a-b = float|int

   Fn.plus(a float|int, b float|int) -> a+b = float|int

   Fn.div(a float|int, b float|int) -> a/b = float|int

   Fn.str(a any) -> string or '' if the value cannot be converted to string.

   Fn.float(a any) -> float or 0 if the value cannot be converted to number.

   Fn.int(a any) -> int or 0 if the value cannot be converted to number.

   Fn.ceil(a float) -> int

   Fn.floor(a float) -> int

   Fn.round(a float) -> int

   Fn.max(a float, b float) -> float if a>b then a else b

   Fn.min(a float, b float) -> float if a<b then a else b

   Fn.eq(a float, b float) -> bool if a=b then true else false

   Fn.ne(a float, b float) -> bool

   Fn.gt(a float, b float) -> bool

   Fn.lt(a float, b float) -> bool

   Fn.ge(a float, b float) -> bool

   Fn.le(a float, b float) -> bool