Skip to content

Formula Language

The platform provides a robust formula language for creating new columns derived from existing data. This language is designed for clarity and expressiveness and follows conventions like those found in spreadsheet environments such as Excel.

Formula Definition

A formula is a structured expression that computes a new value by applying functions and operators to input values. It consists of the following components:

  • Values

    • Column references: Denoted as [Column Name]
    • Literal values: - Strings: "this is a string" - Numbers: 14, 3.14, 0 - Booleans: true, false

  • Operators

  • Arithmetic: +, -, /, *

  • Comparison: <, <=, !=, =, >, >=

  • Functions

  • Syntax: FUNCTION_NAME(value1, value2, ...)

Examples

[total_amount * 0.2]

EMPTY([payment_date])

AND(NOT([payed]), [amount] > 100 )

Logical functions

This category of functions return a boolean value (true or false).

EMPTY

usage: EMPTY(value)

Returns true is value is empty, false otherwise.

    EMPTY([ColumnName])

NOT

usage: NOT (bool_value)

Return the negation of the input value.

    NOT(EMPTY([ColumnName]))

IF

usage: IF (expression, value1, value2)

Returns value1 if expression is true, value2 otherwise.

    IF(NOT(EMPTY([ColumnName])), [ColumnName], "default value")

AND

usage: AND (expression1, expression2...)

This function accepts a variable number of parameters.

It returns true if all the expressions are true.

    AND(NOT(EMPTY([ColumnName])), [Age] >= 18)

OR

usage: OR (expression1, expression2...)

This function accepts a variable number of parameters.

It returns true if any of the expressions are true.

    OR(AND([Age] >= 18, [Country] != "US"), AND([Age] >= 21, [Country] = "US"))

String functions

These functions perform operations on string values.

SUBSTRING

usage: SUBSTRING(stringValue, startPosition, length)

Extracts a substring from the input string starting at the specified position. The length parameter is optional; if omitted, the substring extends to the end of the string.

SUBSTRING("hello", 1) → "ello"

SUBSTRING("google", 1, 2) → "oo"

SUBSTRING("google", 1, 1000) → "oogle"

TO_UPPER/TO_LOWER

uasage: TO_UPPER(stringValue)

Returns a new string where all characters in the input are converted to uppercase/lowercase

    TO_LOWER([payed]) = "y"   → true for "Y" or "y", false otherwise

    TO_UPPER(SUBSTRING([payed], 0, 1)) = "Y" 
      → true if the value in column "payed" starts with "y" or "Y"

LENGTH

usage: LENGTH(stringValue)

Returns the length of a string.

    Length("Google") → 6

Date & time functions

Most of these functions apply to a Date or DateTime value. Some of them apply also to time intervals, obtained by subtracting two dates.

TODAY

usage: TODAY()

No parameter is required. It returns the current date (date only without time).

ADD_DAYS

usage: ADD_DAYS(dateValue, days)

Add (or substract if negative) days to the dateValue.

    ADD_DAYS(TODAY(), -2)   → two days ago
    ADD_DAYS(TODAY(), 1)    → tomorrow

DOW/DOM/DOY

usage: DOW(date)

Returns the Day of Week for the given date. 0 is Sunday, 6 is Saturday.

usage: DOM(date)

Returns the Day of Month for the given date.

usage: DOY(date)

Returns the Day of Year for the given date.

DATE_ONLY

usage: DATE_ONLY(time)

Extracts the date from a timestamp.

SECONDS

usage: SECONDS(time/timespan)

Extracts or computes the number of seconds from a time value or time interval.

  • Time value input: Returns an integer representing the seconds component of the time.
  • Time interval input: Returns a floating-point number representing the total duration in seconds.

MINUTES

usage: MINUTES(time/timespan)

Extracts or computes the number of minutes from a time value or time interval.

  • Time value input: Returns an integer representing the minutes component of the time.
  • Time interval input: Returns a floating-point number representing the total duration in minutes.

HOURS

usage: HOURS(time/timespan)

Extracts or computes the number of hours from a time value or time interval.

  • Time value input: Returns an integer representing the hours component of the time.
  • Time interval input: Returns a floating-point number representing the total duration in hours.
    [Distance] / HOURS([DropoffTime]-[PickupTime])  → the average speed

DAYS

usage: DAYS(time/timespan)

Extracts or computes the number of days from a time value or time interval.

  • Time value input: Returns an integer representing the days component of the time.
  • Time interval input: Returns a floating-point number representing the total duration in days.

MONTH

usage: MONTH(time)

Extracts the month part from a date or time; integer value.

YEAR

usage: YEAR(time)

Extracts the year part from a date or time; integer value.

DATE

usage: DATE(year, month, day)

Creates a date from components.

    DATE(2025, [Month], [Day])

Numerical functions

TO_INT

usage: TO_INT(number/string)

Converts a numeric or string input to an integer.

  • Numeric input: Returns the nearest lower integer (i.e., applies floor rounding).
  • String input: Returns the parsed integer value if the string represents a valid integer; otherwise, returns an empty result.
    TO_INT("300")               → 300
    TO_INT(3.14)                → 3
    TO_INT([Distance] + 0.5)    → the closest integer

ABS

usage: ABS(number)

Returns the absolute value.

    ABS(-3.14)  → 3.14
    ABS(33)  → 33

MIN

usage: MIN(value1, value2, ...)

This function accepts a variable number of parameters. Returns the minimum value.

    MIN([Discount], 0.1)

MAX

usage: MAX(value1, value2, ...)

This function accepts a variable number of parameters. Returns the maxium value.

    MAX([PersonalDiscount], [BlackFridayDiscount], 0.1)

SQRT

usage: SQRT(number)

Computes the square root of number.

POW

usage: POW(value, power)

Raise the value to a power.

    POW(3, 2)       → 9
    SQRT(POW(3,2))  → 3
    POW(27, 1/3)    → 9
    POW(2, -1)      → 0.5