You can use a predefined function and create a formula to calculate the value of a column without writing a script. Use a predefined function or create a nested formula by using the existing predefined functions to calculate the column value type.

Supported operators

The following comparison operators are supported only for number type values.
  • = (Equal to)
  • <> (Not equal to)
  • > (Greater than)
  • < (Less than)
  • >= (Greater than or equal to)
  • <= (Less than or equal to)

Simple math functions

Use simple math functions to perform basic mathematical calculations on numeric value columns.

AVERAGE

Returns the average value of the arguments.

Syntax Input Output
AVERAGE(argument 1, argument 2, … argument n) Numeric value, function call, or variable Numeric value
Examples:
  • Function: AVERAGE(1,2,3)

    The result is 2.

  • Formula: AVERAGE(LENGTH(first_name), LENGTH(last_name))

    The result is the average value of the number of characters in the first_name column and last_name column.

DIVIDE

Returns the final quotient value after consecutively dividing the first argument with the next argument until the function reaches the last argument.

Syntax Input Output
DIVIDE(argument 1, argument 2 … argument n) Numeric value, function call, or variable Numeric value
Examples:
  • Function: DIVIDE(10,20, 0.25, 10)

    The result is 0.2.

  • Formula: DIVIDE(LENGTH(full_name),2)

    The result is the number of characters in the full_name column divided by 2.

INDEXMATCH

Retrieves the first not null value from the specified set of arguments.
Syntax Input Output
INDEXMATCH(argument 1, argument 2 , … argument n)

String, numeric value, function call, or variable.

 Numeric value
Example:

Function: INDEXMATCH(""," ",2,"string")

The result is 2.

MAX

Returns the highest value in the specified arguments.

Syntax Input Output
MAX(argument 1, argument 2, … argument n) Numeric value, function call, or variable Numeric value
Examples:
  • Function: MAX(1, -5, 20, 6)

    The result is 20.

  • Formula: MAX(LENGTH(first_name), LENGTH(last_name))

    The result is the number of characters in the first_name column or last_name column whichever is the highest.

MIN

Returns the lowest value in the specified arguments.

Syntax Input Output
MIN(argument 1, argument 2, … argument n) Numeric value, function call, or variable Numeric value
Examples:
  • Function: MIN(1, -5, 20, 6)

    The result is -5.

  • Formula: MIN(LENGTH(first_name), LENGTH(last_name))

    The result is the number of characters in the first_name column or last_name column whichever is the lowest.

MULTIPLY

Returns the total multiplied value of the arguments.

Syntax Input Output
MULTIPLY (argument 1, argument 2, … argument n) Numeric value, function call, or variable Numeric value
Examples:
  • Function: MULTIPLY(12, 4)

    The result is 48.

  • Formula: MULTIPLY(order, 2)

    The result is the order column value multiplied by 2.

POWER

Returns the result of the base value raised to the power of the exponent value.

Syntax Input Output
POWER(argument 1, argument 2) argument 1 is base and argument 2 is exponent.
  • base: number or variable
  • exponent: number or variable
 Number
Examples:
  • Function: POWER(3,2)

    The result is 9.

  • Formula: POWER(LENGTH(full_name),2)

    The result is the number of characters in the full_name column to the power of 2.

SUBTRACT

Returns the result value after consecutively subtracting the next available argument from the earlier argument until the function reaches last argument.

Syntax Input Output
SUBTRACT(argument 1, argument 2 … argument n) Numeric value, function call, or variable Numeric value
Examples:
  • Function: SUBTRACT(1.15, 0.02, 0.45, -0.85)

    The result is 1.53.

  • Formula: SUBTRACT(LENGTH(full_name), LENGTH(first_name))

    The result is the number of characters from the full_name column minus the number of characters from the first_name column.

SUM

Returns the sum of all the arguments.

Syntax Input Output
SUM(argument 1,argument 2, ... argument n) Numeric value, function call, or variable Numeric value
Examples:
  • Function: SUM(0.03, -0.02, 1)

    The result is 1.01.

  • Formula: SUM(LENGTH(first_name), LENGTH(last_name))

    The result is the total number of characters in the first_name column plus the total number of characters in the last_name column.

COUNTIF

Returns the number of arguments that match the specified criteria within the specified set of arguments.
Syntax Input Output
COUNTIF(argument 1, argument 2, argument n-1, criteria)
  • argument 1 … argument n: String, numeric value, function call, or variable.
  • criteria: Criteria that evaluates the specified set of arguments. String, numeric value, function call, or variable.
 Numeric value
Example:

Function: COUNTIF(2,3,2,"string",2)

The result is 2.

MODE

Returns the most frequently repeating value in the specified set of arguments.
Syntax Input Output
MODE(argument 1,argument 2, ... argument n) Numeric value, function call, or variable Numeric value
Example:

Function: MODE(1, 2, 2, 3, 3, 3)

The result is 3.

String functions

Use string functions to reformat or perform calculations on string column values.

CONCATENATE

Joins one or more input strings into a single string.

Syntax Input Output
CONCATENATE(string 1, string 2, … string n) String, function call, or variable String
Examples:
  • Function: CONCATENATE(first_name, ".", last_name, "@", LOWERCASE(example), ".com")

    The result is the concatenated value <first_name_value>.<last_name_value>@example.com. In this example, <first_name_value> and <last_name_value> are placeholders.

  • Function: CONCATENATE(first_name, " ", last_name)

    The result is the concatenated string of first_name column value and last_name column value separated by a white space.

ISBLANK

Finds white spaces or blank values in the string and returns true if there are any.

Syntax Input Output
ISBLANK(argument) String or value True or false
Examples:
  • Function: ISBLANK(“example_string”)

    The result is false.

  • Function: ISBLANK(full_name)

    The result is true only when there are empty spaces in the full_name column. Otherwise, the result is false.

LENGTH

Returns the total number of characters in the input string.

Syntax Input Output
LENGTH(argument) String value, function call, or variable Numeric value
Examples:
  • Function: LENGTH("example_string")

    The result is 14.

  • Function: LENGTH(full_name)

    The result is the total number of characters in the full_name column value.

LOWERCASE

Converts the input string to all lowercase characters.

Syntax Input Output
LOWERCASE(argument) String, function call, or variable String in lowercase
Examples:
  • Function: LOWERCASE(“ExamPle inpuT stRing”)

    The result is example input string.

  • Function: LOWERCASE(sys_created_by)

    The result is the lower case string of the sys_created_by column value.

REPLACE

Replaces the characters in the source string with the characters in the target string.

Syntax Input Output
REPLACE(source_string, target_string, replacement_string)
  • source_string: String, function call, or variable
  • target_string: String, function call, or variable
  • replacement_string: String, function call, or variable
 String
Examples:
  • Function: REPLACE(“Pepperoni Pizza”, “Pepperoni”, “Cheese”)

    The result string is Cheese Pizza.

  • Function: REPLACE("abe.tuter@example.com", "example", company_name)

    The result string is abe.tuter@<company_name>.com. In this example, <company_name>is a place holder.

TITLECASE

Converts the input string to all title case characters.

Syntax Input Output
TITLECASE(argument) String, function call, or variable String in title case
Examples:
  • Function: TITLECASE("example string")

    The result is Example String.

  • Function: TITLECASE(full_name)

    The result is the full name column value in the title case.

UPPERCASE

Converts the input string to all uppercase characters.

Syntax Input Output
UPPERCASE(argument) String value, function call, or variable String in uppercase
Examples:
  • Function: UPPERCASE("eXamPle sTring")

    The result is EXAMPLE STRING.

  • Function: UPPERCASE(state)

    The result is the State column value in upper case.

FIND

Searches for the first occurrence of a substring within a string and returns the position of the first occurrence.
Note: This function is case sensitive.
Syntax Input Output
FIND(search_string, source_string, from_index)
  • search_string: Substring, function call, or variable.
  • source_string: Main string, function call, or variable.
  • from_index: Index position in the main string from where the search should start. Numeric value, function call, or variable.
 Numeric value (integer). When the substring does not exist in the main string, -1 is returned.
Example:

Function: FIND("morning", "Good morning")

The result is 5.

SEARCH

Searches for a substring within a string and returns the position of the first occurrence of the substring.
Note: This function is case insensitive.
Syntax Input Output
SEARCH(search_string, source_string, from_index)
  • search_string: Substring, function call, or variable.
  • source_string: Main string, function call, or variable.
  • from_index: Index position in the main string from where the search should start. Numeric value, function call, or variable.
 Numeric value (integer). When the substring does not exist in the main string, -1 is returned.
Examples:
  • SEARCH("Morning", "Good morning")

    The result is 5.

  • SEARCH("World","Hello world!")

    The result is -1.

SUBSTRING

Retrieves a substring from a string at the specified index position and for the specified length.
Syntax Input Output
SUBSTRING(source_string, start_index, length)
  • source_string: String, function call, or variable.
  • start_index: Position in the string from where the substring is extracted. Numeric value, function call, or variable.
  • length: Length of the substring that should be extracted.
 String
Example:

SUBSTRING("Hello, Good Morning", 7, 4)

The result substring is 'Good'.

Date and time functions

Use date and time functions to calculate or reformat the date and time column values.

NOW

Returns the current date and time of the instance in ISO format (YYYY-MM-DD hh:mm:ss).

Syntax Input Output
NOW() No arguments are required for this function ISO format of current date and time
Example:

Function: NOW()

The result is the current date and time in ISO format.

TODAY

Returns the current date with time offset to start of the day in ISO format in UTC time zone.
Syntax Input Output
TODAY() No arguments are required for this function Current date with time offset to start of the day in ISO format.
Example:

Function: TODAY()

The result is the current date and start time of the day in ISO format.

TIMEDIFF

Evaluates the time duration difference between two dates.

Syntax Input Output
TIMEDIFF(argument1, argument2) Date in ISO format (YYYY-MM-DD hh:mm:ss) as string or variable Duration
Examples:
  • Function: TIMEDIFF("2021-05-02 9:10:12", "2021-04-07 6:2:23")

    The result is 25 03:07:49.

  • Formula: TIMEDIFF(sys_created_on, NOW())

    The result is the time duration difference between the sys_created_on date and the current date of the system.

DATEDIF

Evaluates the difference between the two dates in days, months, or years.
Syntax Input Output
DATEDIF(start_date, end_date, date_difference_unit)
  • start_date: Date in ISO format (YYYY-MM-DD or YYYY-MM-DD hh:mm:ss) as string or variable.
  • end_date: Date in ISO format (YYYY-MM-DD or YYYY-MM-DD hh:mm:ss) as string or variable.
  • date_difference_unit: Character String and either "Y","M", or "D" in lowercase or uppercase. Default is "D".
 Numeric duration value based on the specified date difference unit.
Example:

Function: DATEDIF("2021-05-02 9:10:12", "2021-05-05 6:2:23 ","d")

The result is 3.

DATE

Creates a date from the specified individual year, month, and day values. The created date is in Coordinated Universal Time (UTC) time zone.
Syntax Input Output
DATE(year,month,day)
  • year: Numeric value, variable or function.
  • month: Numeric value, variable or function.
  • day: Numeric value, variable or function.
 Date in ISO format (YYYY-MM-DD hh:mm:ss)
Example:

Function: DATE(2021,5,2)

The result is 2021-05-02 00:00:00.

DAY

Retrieves the numerical day component from the specified date.
Syntax Input Output
DAY(date) Date in ISO format (YYYY-MM-DD or YYYY-MM-DD hh:mm:ss) as string, variable, or function. Numeric value (integer). The values range from 1 through 31.
Examples:
  • Function: DAY("2021-05-029:10:12")

    The result is 2.

  • Function: DAY(NOW())

    The result will be the day component of the current date and time.

MONTH

Retrieves the numerical month component from the specified date.
Syntax Input Output
MONTH(date) date: Date in ISO format (YYYY-MM-DD or YYYY-MM- DD hh:mm:ss) as string or variable. Numeric value (integer). The values range from 1(January) through 12(December).
Examples:
  • Function: MONTH("2021-05-02 9:10:12")

    The result is 5.

  • Function: DAY(NOW())

    The result will be the month component of the current date and time.

YEAR

Retrieves the year component from the specified date.
Syntax Input Output
YEAR(date) Date in ISO format (YYYY-MM-DD or YYYY-MM-DD hh:mm:ss) as string, variable, or function. Numeric value (integer)
Examples:
  • Function: YEAR("2021-05-02 9:10:12")

    The result is 2021.

  • Function: YEAR(NOW())

    The result will be the year component of the current date and time.

WEEKDAY

Returns the numerical day of the week for the specified date. The day range is 1 (Sunday) through 7 (Saturday).
Syntax Input Output
WEEKDAY(date) date: Date in ISO format (YYYY-MM-DD or YYYY-MM- DD hh:mm:ss) as string or variable. Numeric value (integer)
Example:

Function: WEEKDAY("2021-05-02 9:10:12")

The result is 1.

TEXT

Retrieves the specific date components in a date in string format.
Syntax Input Output
TEXT(date, format_text)
  • date: Date in ISO format (YYYY-MM-DD or YYYY-MM-DD hh:mm:ss) as string, variable.
  • format_text: Date components as string or variable that are to be extracted.
 String
Example:

TEXT("2022-08-17 9:10:12","yyyy-MM")

The result is 2022-08.

DATEVALUE

Converts a date in text format into a date in ISO format.
Syntax Input Output
DATEVALUE(date_text) date_text: Date stored as text must be in YYYY-MM-DD format.

Date in ISO format (YYYY-MM-DD hh:mm:ss) as string.
Example:

Function: DATEVALUE("2021-05-02")

The result is 2021-05-02 00:00:00.

WORKDAY

Returns the nearest working day for the specified input date by excluding the specified holidays and weekends before or after the specified n number of days.
Syntax Input Output
WORKDAY(start_date, days, holiday 1,holiday 2, ..., holiday n)
  • start_date: Date in ISO format (YYYY-MM-DD or YYYY-MM-DD hh:mm:ss) as string or variable.
  • days: Number of days as a numeric value, string, or function.
  • holiday 1...holiday n (Optional): Date in ISO format (YYYY-MM-DD or YYYY-MM-DD hh:mm:ss) as string or variable.

Date in ISO format (YYYY-MM-DD hh:mm:ss) as string.
Example:

Function: WORKDAY("2022-08-17 9:10:12",2)

The result is 2022-08-19 00:00:00.

NETWORKDAYS

Calculates the number of working days between two dates by excluding weekends and specified holiday dates. Number of working days includes the start date and end date.
Syntax Input Output
NETWORKDAYS(start_date,end_date,holiday 1,holiday 2, ... holiday n)
  • start_date: Date in ISO format (YYYY-MM-DD or YYYY-MM-DD hh:mm:ss) as string or variable.
  • end_date: Date in ISO format (YYYY-MM-DD or YYYY-MM-DD hh:mm:ss) as string or variable.
  • holiday 1,holiday 2, ... holiday n (optional) : List of holidays that should be excluded while calculating working days.
 Numeric value (integer)
Example:

Function: NETWORKDAYS("2022-08-17 20:10:12","2022-08-19 9: 10:12")

The result is 3.

Logical functions

Use logical functions to perform logical operations on column values.

AND

Performs a logical AND operation on the arguments.

Syntax Input Output
AND(argument 1, argument 2) String, function call, or variable True or false
Examples:
  • Function: AND(2>3, 4<5)

    The result is false.

  • Formula: AND(LENGTH(sys_created_by)>25, LENGTH(sys_updated_by)>25)

    The result is true only when the number of characters in both the sys_created_by and sys_updated_by columns are greater than 25. Otherwise, the result is false.

IF

Executes the specified statements based on the Boolean output of the conditional expression.

Syntax Input Output
IF(<conditional_expression>, <do_this_when_true>, <do this_when_false>)
  • conditional_expression: Logical conditional expression, function call, or variable
    Note: Logical comparison of strings is not supported in the conditional expression.
  • do_this_when_true: String, numeric value, function call, or variable that is returned when the condition evaluates to true
  • do_this_when_false: String, numeric value, function call, or variable that is returned when the condition evaluates to false
 String, numeric value, function call, or variable based on the Boolean output of the conditional expression.
Examples:
  • Function: IF(number_of_incidents >= 5, "High", "Medium")

    If the number of incidents is greater than 5, the string ‘High’ is returned. In other cases, the string ‘Medium’ is returned.

  • Function: IF(LENGTH(full_name) > 100, "Number of characters exceed the limit", "Number of characters within the limit")

    If the number of characters for the full_name column is above 100, the string 'Number of characters exceed the limit' is returned. Otherwise, the string 'Number of characters within the limit' is returned.

OR

Performs logical OR operation on the arguments.

Syntax Input Output
OR(argument 1, argument 2) Conditional expression, function call, or variable True or false
Examples:
  • Function: OR(2>3,4<5)

    The result is true.

  • Formula: OR(LENGTH(first_name)>25, LENGTH(last_name)<25)

    The result is true when the number of characters in the first_name column is greater than 25 or the number characters in the last_name column is less than 25. Otherwise, the result is false.

IFERROR

Evaluates expression 1 and returns the expression 1 value when there are no errors in the expression 1. When an error occurs while evaluating expression 1, expression 2 is evaluated and expression 2 value is returned.
Syntax Input Output
IFERROR(expression 1, expression 2)
  • expression 1: Arithmetic, Logical expression, function call, String, numeric value or variable.
  • expression 2: Arithmetic, Logical expression, function call, String, numeric value or variable.
 Result of expression 1 when there are no errors in expression 1. Otherwise, result of expression 2.
Example:

Function: IFERROR( MULTIPLY(snr_factor, signal), MULTIPLY( default_factor, signal))

If the snr_factor value is a valid number, the multiplied value of snr_factor with signal is returned. If the snr_factor value is not a valid number, the multiplied value of default_factor value with signal is returned.