Formula Function Reference

A comprehensive reference for every operator and function you can use to build formulas in Invoca’s Formula Assistant.

Invoca lets you create derived columns in Interaction Details using formulas, built with the Formula Assistant. An individual formula consists of any number of combinations of operators and functions. This reference lists the operators and functions you can use, grouped by type.

Aggregate functions (group aggregate)

Function	Description	Example
`average`	Average of all values of a column	`average (revenue)`
`average_if`	Average of columns meeting a criteria	`average_if(city = "San Francisco", revenue)`
`count`	Number of rows in the table containing the column	`count (product)`
`count_if`	Number of rows meeting a specified condition	`count_if(region = 'west', region)`
`cumulative_average`	Average of the Measure, accumulated by attribute(s) in the order specified	`cumulative_average (revenue, order date, state)`
`cumulative_max`	Maximum of the Measure, accumulated by attribute(s) in the order specified	`cumulative_max (revenue, state)`
`cumulative_min`	Minimum of the Measure, accumulated by attribute(s) in the order specified	`cumulative_min (revenue, campaign)`
`cumulative_sum`	Sum of the Measure, accumulated by attribute(s) in the order specified	`cumulative_sum (revenue, order date)`
`group_aggregate`	Aggregates a Measure with different granularities and filters than the columns used in the search. Define lists with `{ }`. Optional `query_groupings()` / `query_filters()` reuse the original search’s lists; use `+` / `-` to add or exclude columns	`group_aggregate (sum (revenue), {ship mode, date}, {} )`
`group_average`	Average of the Measure grouped by attribute(s)	`group_average (revenue, customer region, state)`
`group_count`	Count of the Measure grouped by attribute(s)	`group_count (revenue, customer region)`
`group_max`	Maximum of the Measure grouped by attribute(s)	`group_max (revenue, customer region)`
`group_min`	Minimum of the Measure grouped by attribute(s)	`group_min (revenue, customer region)`
`group_stddev`	Standard deviation of the Measure grouped by attribute(s)	`group_stddev (revenue, customer region)`
`group_sum`	Sum of the Measure grouped by attribute(s)	`group_sum (revenue, customer region)`
`group_unique_count`	Unique count of the Measure grouped by attribute(s)	`group_unique_count (product, supplier)`
`group_variance`	Variance of the Measure grouped by attribute(s)	`group_variance (revenue, customer region)`
`max`	Maximum value of a column	`max (sales)`
`max_if`	Maximum value among columns meeting a criteria	`max_if( (revenue > 10), customer region )`
`median`	Value of the Measure from the row at the 50th percentile. See limitations below	`median (sales)`
`min`	Minimum value of a column	`min (revenue)`
`min_if`	Minimum value among columns meeting a criteria	`min_if( (revenue < 10), customer region )`
`moving_average`	Average of the Measure over a window (current − Num1 … current + Num2, inclusive; a negative Num2 defines a window before the current row)	`moving_average (revenue, 2, 1, customer region)`
`moving_max`	Maximum of the Measure over the given window	`moving_max (complaints, 1, 2, store name)`
`moving_min`	Minimum of the Measure over the given window	`moving_min (defects, 3, 1, product)`
`moving_sum`	Sum of the Measure over the given window	`moving_sum (revenue, 1, 1, order date)`
`percentile`	Value of the Measure from the row with a `rank_percentile` ≤ N (or the first row above N, if none is below it)	`percentile (sales, 99, 'asc')`
`rank`	Rank for the current row; identical values receive an identical rank. First argument is an aggregate; second is `'asc'` or `'desc'`	`rank (sum (revenue), 'asc')`
`rank_percentile`	Percentile rank for the current row; identical values receive an identical percentile rank	`rank_percentile (sum (revenue), 'asc')`
`stddev`	Standard deviation of all values of a column	`stddev (revenue)`
`stddev_if`	Standard deviation of values meeting a criteria	`stddev_if( (revenue > 10), (revenue/10.0) )`
`sum`	Sum of all values of a column	`sum (revenue)`
`sum_if`	Sum of values meeting a criteria	`sum_if(region='west', revenue)`
`unique count`	Number of unique values of a column	`unique count (customer)`
`unique_count_if`	Number of unique values meeting a condition	`unique_count_if( (revenue > 10), order date )`
`variance`	Variance of all values of a column	`variance (revenue)`
`variance_if`	Variance of values meeting a criteria	`variance_if( (revenue > 10), (revenue/10.0) )`

Important: median isn’t supported when the resulting query is a complex query (chasm/fan trap) with a non-shared attribute (an attribution query); has group-by-group_aggregate; has a reusable set; has a query with an IN-filter query; is displayed as a pivot table; or is a table aggregate summary over a median column.

Conversion functions

Convert data from one data type to another. Invoca doesn’t support date data type conversion.

to_bool, to_date, to_double, to_integer, to_string

Function	Description	Example
`to_bool`	Returns the input as a `boolean` (`true`/`false`)	`to_bool (0) = false`
`to_date`	Accepts a date as an integer or text string, plus a `strptime`-formatted string parameter. Doesn’t accept epoch-formatted dates or datetime values (only month, day, year)	`to_date (date_sold, '%Y-%m-%d')`
`to_double`	Returns the input as a `double`	`to_double ('3.14') = 3.14`
`to_integer`	Returns the input as an integer	`to_integer ('45') + 1 = 46`
`to_string`	Returns the input as text. To convert a date, specify the format in the second argument using strftime	`to_string (45 + 1) = '46'`

Date functions

add_days, add_minutes, add_months, add_seconds, add_weeks, add_years,
date, day, day_number_of_quarter, day_number_of_week, day_number_of_year,
day_of_week, diff_days, diff_time, hour_of_day, is_weekend, month,
month_number, month_number_of_quarter, now, quarter_number,
start_of_month, start_of_quarter, start_of_week, start_of_year, time,
today, week_number_of_month, week_number_of_quarter, week_number_of_year,
year

See About date formulas for the full list with descriptions and examples, including which functions accept an optional 'fiscal' / 'calendar' parameter.

Mixed functions

Work with both text and numeric data types: =, >=, >, greatest, least, <=, <, !=.

Function	Description	Example
`=`	`true` if the first value equals the second	`2 = 2 = true`
`>`	`true` if the first value is greater than the second	`3 > 2 = true`
`>=`	`true` if the first value is greater than or equal to the second	`3 >= 2 = true`
`greatest`	The larger of the values	`greatest (20, 10) = 20`
`least`	The smaller of the values	`least (20, 10) = 10`
`<`	`true` if the first value is less than the second	`3 < 2 = false`
`<=`	`true` if the first value is less than or equal to the second	`1 <= 2 = true`
`!=`	`true` if the first value doesn’t equal the second	`3 != 2 = true`

Number functions

*, +, -, /, power, abs, acos, asin, atan, atan2, cbrt, ceil, cos, cube,
exp, exp2, floor, ln, log10, log2, mod, pow, random, round, safe_divide,
sign, sin, spherical_distance, sq, sqrt, tan

Function	Description	Example
`*` (multiply)	Multiplies two numbers	`3 * 2 = 6`
`+` (add)	Adds two numbers	`1 + 2 = 3`
`-` (subtract)	Subtracts the second number from the first	`3 - 2 = 1`
`/` (divide)	Divides the first number by the second	`6 / 3 = 2`
`^`	First number raised to the power of the second	`3 ^ 2 = 9`
`abs`	Absolute value	`abs (-10) = 10`
`acos`	Inverse cosine, in degrees	`acos (0.5) = 60`
`asin`	Inverse sine, in degrees	`asin (0.5) = 30`
`atan`	Inverse tangent, in degrees	`atan (1) = 45`
`atan2`	Inverse tangent, in degrees	`atan2 (10, 10) = 45`
`cbrt`	Cube root	`cbrt (27) = 3`
`ceil`	Rounds up to the nearest integer	`ceil (5.9) = 6`
`cos`	Cosine of an angle in degrees	`cos (63) = 0.45`
`cube`	Cube of a number	`cube (3) = 27`
`exp`	Euler’s number (~2.718) raised to a power	`exp (2) = 7.38905609893`
`exp2`	2 raised to a power	`exp2 (3) = 8`
`floor`	Rounds down to the nearest integer	`floor (5.1) = 5`
`ln`	Natural logarithm	`ln (7.38905609893) = 2`
`log10`	Base-10 logarithm	`log10 (100) = 2`
`log2`	Base-2 logarithm	`log2 (32) = 5`
`mod`	Remainder of a division	`mod (8, 3) = 2`
`pow`	First number raised to the power of the second	`pow (5, 2) = 25`
`random`	Random number between 0 and 1	`random ( ) = .457718`
`round`	First number rounded to the second number (default 1)	`round (35.65, 10) = 40`
`safe_divide`	Divides the first number by the second; returns 0 (not NaN) if the second is 0	`safe_divide (12, 0) = 0`
`sign`	+1, -1, or 0, based on the number’s sign	`sign (-250) = -1`
`sin`	Sine of an angle in degrees	`sin (35) = 0.57`
`spherical_distance`	Distance in km between two lat/long points (`lat1, long1, lat2, long2`)	`spherical_distance (37.465191, -122.153617, 37.421962, -122.142174) = 4,961.96`
`sq`	Square of a number	`sq (9) = 81`
`sqrt`	Square root	`sqrt (9) = 3`
`tan`	Tangent of an angle in degrees	`tan (35) = 0.7`

Operators

Operator	Description	Example
`and`	`true` when both conditions are `true`. Not available for row-level security (RLS) formulas	`(1 = 1) and (3 > 2) = true`
`if...then...else`	Conditional operator; allows multiple clauses	`if (cost > 500) then 'flag' else 'approve'`
`ifnull`	First value if not `null`, otherwise the second	`ifnull (cost, 'unknown')`
`in`	`true` if the column value matches one of a list of values	`state in { 'texas', 'california' }`
`isnull`	`true` if the value is `null`	`isnull (phone)`
`not`	`true` if the condition is `false`	`not (3 > 2) = false`
`not in`	`true` if the column value doesn’t match any of a list of values	`state not in { 'texas', 'california' }`
`or`	`true` when either condition is `true`	`(1 = 5) or (3 > 2) = true`

Text functions

concat, contains, edit_distance, edit_distance_with_cap, left, right,
similar_to, similarity, sounds_like, spells_like, strlen, strpos, substr

Function	Description	Example
`concat`	Concatenates two or more values into a string	`concat ('hay', 'stack') = 'haystack'`
`contains`	`true` if the first string contains the second	`contains ('broomstick', 'room') = true`
`edit_distance`	Minimum operations to transform one string into another (strings under 1023 characters)	`edit_distance ('attorney', 'atty') = 4`
`edit_distance_with_cap`	Edit distance, capped at an integer; returns cap + 1 if the distance exceeds it	`edit_distance_with_cap ('pokemon go', 'minecraft pixelmon', 3) = 4`
`left`	Portion of a string of a given length, from the left	`left ('persnickety', 4) = 'pers'`
`right`	Portion of a string of a given length, from the right	`right ('persnickety', 4) = 'kety'`
`similar_to`	`true` if the relevance score between two strings is at least 20	`similar_to ('hello world', 'hello swirl') = true`
`similarity`	Relevance score (0-100) between a document string and a search string; 100 for an exact match	`similarity ('where is the burning man concert', 'burning man') = 46`
`sounds_like`	`true` if two strings sound similar when spoken	`sounds_like ('read', 'red') = true`
`spells_like`	`true` if two strings (under 1023 characters) are spelled similarly	`spells_like ('thouhgtspot', 'Invoca') = true`
`strlen`	Length of a string	`strlen ('smith') = 5`
`strpos`	Position (from 0) of the first occurrence of the second string in the first, or -1 if not found	`strpos ('haystack_with_needles', 'needle') = 14`
`substr`	Portion of a string, from a given position (from 0), of a given length	`substr ('persnickety', 3, 7) = snicket`

Variables

Variable	Description	Example
`ts_groups`	List of groups the current logged-in user belongs to. If the expression evaluates to `true` for any of the user’s groups, they can see that row	`ts_groups = 'east'`
`ts_username`	The user with the matching name	`ts_username != 'mark'`

Note: You can’t use ts_groups or ts_username inside another expression. For example, ts_groups = substr(rls_group_name, 0, 3) is valid, but substr(ts_groups,0,3) = rls_group_name is not.

Embrace passthrough functions

The following passthrough SQL functions are supported in connections for all cloud data warehouses:

sql_bool_aggregate_op, sql_bool_op, sql_date_aggregate_op, sql_date_op,
sql_date_time_aggregate_op, sql_date_time_op, sql_double_aggregate_op,
sql_double_op, sql_int_aggregate_op, sql_int_op,
sql_string_aggregate_op, sql_string_op, sql_time_aggregate_op, sql_time_op

Each passthrough function’s first argument is the signature of the external function to execute against the data source; subsequent arguments pass values to it. For example:

sql_bool_aggregate_op ("booland_agg ({0})", is_delivered)
sql_int_op ("charindex ({0}, {1})", "qwerty", "rty") = 4
sql_double_op ("radians ({0})", 180) = 3.141592654

Each variant (bool, date, date_time, double, int, string, time; aggregate and non-aggregate) returns the corresponding data type.

Getting Started

Implementation & The Invoca Tag

Campaign & Call Handling

Insights & Analytics

Formula Function Reference

Aggregate functions (group aggregate)

Conversion functions

Date functions

Mixed functions

Number functions

Operators

Text functions

Variables

Embrace passthrough functions

Where to go next

​Aggregate functions (group aggregate)

​Conversion functions

​Date functions

​Mixed functions

​Number functions

​Operators

​Text functions

​Variables

​Embrace passthrough functions

​Where to go next

Aggregate functions (group aggregate)

Conversion functions

Date functions

Mixed functions

Number functions

Operators

Text functions

Variables

Embrace passthrough functions

Where to go next