Formula Functions

Formula functions can be used in report custom columns to calculate values, compare results, filter rows, format text, and work with values from nearby rows. Use the Formula Builder on the Custom Columns tab to insert fields and functions into a formula.

Custom formula comments can start with // when a note is needed inside a formula.

Formula Functions
FUNCTION	DESCRIPTION
if( expression, then, else )	Returns one value when the expression is true and another value when it is false.
or( expression, expression )	Returns true when at least one expression is true.
and( expression, expression )	Returns true when all expressions are true.
not( expression )	Reverses a true or false result.
abs( expression )	Returns the absolute value.
power( expression, exponent )	Raises a value to the specified exponent.
mod( expression, divisor )	Returns the remainder after division.
sqrt( expression )	Returns the square root.
ln( expression )	Returns the natural logarithm.
log( expression )	Returns the logarithm.
random( min, max )	Returns a random number within the specified range.
min( expression, expression )	Returns the lower value.
max( expression, expression )	Returns the higher value.
floor( expression )	Rounds down to the nearest whole number.
ceil( expression )	Rounds up to the nearest whole number.
round( expression, precision )	Rounds a value to the specified precision.
round_difference( expression, precision )	Returns the difference between the rounded and non-rounded value.
round_time( expression, round_interval, direction )	Rounds time using the selected interval and direction: 10=Down, 20=Average, 30=Up.
range_translate( key, value )	Returns a value based on the range that matches the key.
time	Returns the current time.
date( Year, Month, Day, Hour, Minute )	Builds a date/time value.
parse_date( string )	Parses a date string into a timestamp value.
year(), month(), day(), hour(), minute(), second()	Returns the matching part of a timestamp value.
day_of_week( timestamp )	Returns the day of week as a number where 0=Sunday.
count_day_of_week( start, end, day )	Counts a specific day of week within a date range.
string_contains( string, search )	Checks whether the string contains the search value.
string_match( string, search )	Checks whether the string matches the search value.
string_substring( string, start, end )	Returns part of a string.
string_concat( separator, expression, expression )	Combines values into one string using the selected separator.
string_pad( string, length, pad_string, pad_type )	Pads a string to the selected length.
string_translate( key, value )	Translates a key into a configured value.
string_replace( string, search, replace )	Replaces matching text in a string.
string_to_upper( string )	Converts text to uppercase.
string_to_lower( string )	Converts text to lowercase.
literal( string )	Treats text as a literal string value.
is_empty( expression )	Checks whether the value is empty.
get_row_num()	Returns the current row number being evaluated, starting at 0.
get_previous_row_column( column )	Retrieves the specified column value from the previous row.
get_next_row_column( column )	Retrieves the specified column value from the next row.
is_first_row()	Returns 1 if the current row is the first row, otherwise 0.
is_last_row()	Returns 1 if the current row is the last row, otherwise 0.
get_global_var( string )	Retrieves a global variable value that was set by the formula.
set_global_var( string, value )	Stores a global variable value for use elsewhere in the formula.
running_total( amount, bucket )	Calculates a running total for the selected amount and bucket.