Functions and Operators

Expressions are used to perform calculations for:

Table calculations (which include expressions used in data tests)
Custom fields
Custom filters

A major part of these expressions is the functions and operators that you can use in them. The functions and operators can be divided into a few basic categories:

Mathematical: Number-related functions
String: Word- and letter-related functions
Dates: Date- and time-related functions
Logical transformation: Includes boolean (true or false) functions and comparison operators
Positional transformation: Retrieving values from different rows or pivots

Some Functions Are Only Available for Table Calculations

Expressions for custom filters and custom fields do not support functions that convert datatypes, aggregate data from multiple rows, or refer to other rows or pivot columns. These functions are supported only for table calculations (including table calculations used in the expression parameter of a data test).

This page is organized to clarify which functions and operators are available, depending on where you are using your expression.

Mathematical Functions and Operators

Mathematical functions and operators work in one of two ways:

Some mathematical functions perform calculations based on a single row. For example, rounding, adding, multiplying, and similar functions can be used for values in a single row, returning a distinct value for each and every row. All mathematical operators, such as +, are applied one row at a time.
Other mathematical functions, like averages and running totals, operate over many rows. These functions take many rows and reduce them to a single number, then display that same number on every row.

Functions for Any Expression

FUNCTION	SYNTAX	PURPOSE
round	round (value, num_decimals)	Returns value rounded to num_decimals decimal places. For examples using round, see the Using pivot_index in table calculations and Standard Deviation and Simple Time Series Outlier Detection Using Table Calculations Community topics.

FUNCTION

SYNTAX

PURPOSE

round

round (value, num_decimals)

Returns value rounded to num_decimals decimal places.

For examples using round, see the Using pivot_index in table calculations and Standard Deviation and Simple Time Series Outlier Detection Using Table Calculations Community topics.

Functions for Table Calculations Only

Warning

Many of these functions operate over many rows and will only consider the rows returned by your report.

FUNCTION	SYNTAX	PURPOSE
count	count (expression)	Returns the count of non-null values in the column defined by expression, unless expression defines a column of lists, in which case returns the count in each list.
count_distinct	count_distinct (expression)	Returns the count of distinct non-null values in the column defined by expression, unless expression defines a column of lists, in which case returns the count in each list.
max	max(expression)	Returns the max of the column created by expression unless expression defines a column of lists, in which case returns the max of each list. For examples using max, see the Using lists in table calculations and Grouping by a dimension in table calculations Community topics.
mean	mean(expression)	Returns the mean of the column created by expression unless expression defines a column of lists, in which case returns the mean of each list. For examples using mean, see the Calculating Moving Averages Help Center article and the Standard Deviation and simple time series outlier detection using Table Calculations Community topic.
min	min(expression)	Returns the min of the column created by expression unless expression defines a column of lists, in which case returns the min of each list.
percent_rank	percent_rank(column, value)	Returns the rank of value in column as a percentage from 0 to 1 inclusive.
percentile	percentile (value_column,percentile_value)	Returns the value from the column created by expression corresponding to the given percentile_value, unless expression defines a column of lists, in which case returns the percentile value for each list. percentile_value must be between 0 and 1; otherwise returns null.
rank	rank (value, expression)	Returns the rank of value in the column created by expression.
rank_avg	rank_avg(value, expression)	Returns the average rank of value in the column created.
running_total	running_total(value_column)	Returns a running total of the values in the value_column.
sum	sum(expression)	Returns the sum of the column created by expression unless expression defines a column of lists, in which case returns the sum of each list.

Operators for Any Expression

You can use the following standard mathematical operators:

OPERATOR	SYNTAX	PURPOSE
+	value_1 + value_2	Adds value_1 and value_2.
-	value_1 - value_2	Subtracts value_2 from value_1.
*	value_1 * value_2	Multiplies value_1 and value_2.
/	value_1 / value_2	Divides value_1 by value_2.

String Functions

String functions operate on sentences, words, or letters, which are collectively called “strings.” You can use string functions to capitalize words and letters, extract parts of a phrase, check to see if a word or letter is in a phrase, or replace elements of a word or phrase. String functions can also be used to format the data returned in the table.

Functions for Any Expression

FUNCTION	SYNTAX	PURPOSE
concat	concat (value_1, value_2, ...)	Returns value_1, value_2, ..., value_n joined as one string.
contains	contains (string, search_string)	Returns Yes if string contains search_string, and No otherwise.
length	length (string)	Returns the number of characters in string.
lower	lower (string)	Returns string with all characters converted to lowercase.
position	position (string, search_string)	Returns the start index of search_string in string if it exists, and 0 otherwise.
replace	replace (string, old_string, new_string)	Returns string with all occurrences of old_string replaced with new_string.
substring	substring (string, start_position, length)	Returns the substring of string beginning at start_position consisting of length characters.
upper	upper (string)	Returns string with all characters converted to uppercase.

Functions for Table Calculations Only

FUNCTION	SYNTAX	PURPOSE
to_number	to_number(string)	Returns the number represented by string, or null if the string cannot be converted.
to_string	to_string(value)	Returns the string representation of value, or an empty string if value is null.

Date Functions

Date functions enable you to work with dates and times.

Functions for Any Expression

FUNCTION	SYNTAX	PURPOSE
add_days	add_days(number, date)	Adds number days to date.
add_months	add_months(number, date)	Adds number months to date.
date	date(year, month, day)	Returns “year-month-day” date or null if the date would be invalid.
date_time	date_time(year, month, day,hours, minutes, seconds)	Returns “year-month-day hours:minutes:seconds” date or null if the date would be invalid.
diff_days	diff_days(start_date, end_date)	Returns the number of days between start_date and end_date. For an example, see the Using dates in table calculations Community topic.
diff_months	diff_months(start_date, end_date)	Returns the number of months between start_date and end_date. For an example, see the Grouping by a dimension in table calculations Community topic.
extract_days	extract_days(date)	Extracts the days from date. For an example, see the Using dates in table calculations Community topic.
extract_months	extract_months(date)	Extracts the months from date.
now	now()	Returns the current date and time. For examples using now, see the Now() Table Calculation Function Has Better Timezone Handling and Using dates in table calculations Community topics.
trunc_days	trunc_days(date)	Truncates date to days.
trunc_months	trunc_months(date)	Truncates date to months.

Functions for Table Calculations Only

FUNCTION	SYNTAX	PURPOSE
to_date	to_date(string)	Returns the date and time corresponding to string (YYYY, YYYY-MM, YYYY-MM-DD, YYYY-MM-DD hh, YYYY-MM-DD hh:mm, or YYYY-MM-DD hh:mm:ss).

Logical Functions, Operators, and Constants

Logical functions and operators are used to assess whether something is true or false. Expressions using these elements take a value, evaluate it against some criteria, return Yes if the criteria are met, and No if the criteria are not met. There are also various logical operators for comparing values and combining logical expressions.

Functions for Any Expression

FUNCTION	SYTAX	PURPOSE
coalesce	coalesce(value_1, value_2, ...)	Returns the first non-null value in value_1, value_2, ..., value_n if found and null otherwise. For examples using coalesce, see the Creating a running total across rows with table calculations, Creating a percent of total across rows with table calculations, and Using pivot_index in table calculations Community topics.
if	if(yesno_expression,value_if_yes, value_if_no)	If yesno_expression evaluates to Yes, returns the value_if_yes value. Otherwise, returns the value_if_no value. For an example, see the Grouping by a dimension in table calculations Community topic.
is_null	is_null(value)	Returns Yes if value is null, and No otherwise. For an example, see the Using Table Calculations documentation page.

FUNCTION

SYTAX

PURPOSE

coalesce

coalesce(value_1, value_2, ...)

Returns the first non-null value in value_1, value_2, ..., value_n if found and null otherwise.

For examples using coalesce, see the Creating a running total across rows with table calculations, Creating a percent of total across rows with table calculations, and Using pivot_index in table calculations Community topics.

if(yesno_expression,value_if_yes,

value_if_no)

If yesno_expression evaluates to Yes, returns the value_if_yes value. Otherwise, returns the value_if_no value.

For an example, see the Grouping by a dimension in table calculations Community topic.

is_null

is_null(value)

Returns Yes if value is null, and No otherwise.

For an example, see the Using Table Calculations documentation page.

Operators for Any Expression

The following comparison operators can be used with any data type:

OPERATOR	SYNTAX	PURPOSE
=	value_1 = value_2	Returns Yes if value_1 is equal to value_2, and No otherwise.
!=	value_1 != value_2	Returns Yes if value_1 is not equal to value_2, and No otherwise.

The following comparison operators only can be used with numbers and dates:

OPERATOR	SYNTAX	PURPOSE
>	value_1 > value_2	Returns Yes if value_1 is greater than value_2, and No otherwise.
<	value_1 < value_2	Returns Yes if value_1 is less than value_2, and No otherwise.
>=	value_1 >= value_2	Returns Yes if value_1 is greater than or equal to value_2, and No otherwise.
<=	value_1 <= value_2	Returns Yes if value_1 is less than or equal to value_2, and No otherwise.

You also can combine expressions with these logical operators:

OPERATOR	SYNTAX	PURPOSE
AND	value_1 AND value_2	Returns Yes if both value_1 and value_2 are Yes, and No otherwise.
OR	value_1 OR value_2	Returns Yes if either value_1 or value_2 is Yes, and No otherwise.
NOT	NOT value	Returns Yes if value is No, and Yes otherwise.

Warning

These logical operators must be capitalized. Logical operators written in lowercase will not work.

Logical Constants

You can use logical constants in expressions. These constants are always written in lowercase and have the following meanings:

CONSTANT	MEANING
yes	True
no	False
null	No Value

Note that the constants yes and no are the special symbols that mean true or false in expressions. In contrast, using quotes such as in "yes" and "no" creates literal strings with those values.

Logical expressions evaluate to true or false without requiring an if function. For example, this:

if(${field} > 100, yes, no)

is equivalent to this:

${field} > 100

You also can use null to indicate no value. For example, you may want to determine if a field is empty, or assign an empty value in a certain situation. This formula returns no value if the field is less than 1, or the value of the field if it is more than 1:

if(${field} < 1, null, ${field})

Combining AND and OR Operators

AND operators are evaluated before OR operators, if you don’t otherwise specify the order with parentheses.

Positional Functions

When creating table calculations, you can use positional transformation functions to extract information about fields in different rows or pivot columns. You can also create lists and retrieve the current row or pivot column index.

Column and Row Totals for Table Calculations Only

If your Explore contains totals, you can reference total values for columns and rows:

FUNCTION	SYNTAX	PURPOSE
:total	${field:total}	Returns the column total of the field.
:row_total	${field:row_total}	Returns the row total of the field.

Row-Related Functions for Table Calculations Only

Some of these functions use the relative positions of rows, so changing the sort order of the rows affects the results of the functions.

FUNCTION	SYNTAX	PURPOSE
index	index(expression, n)	Returns the value of the nth element of the column created by expression, unless expression defines a column of lists, in which case returns the nth element of each list.
list	list(value_1, value_2, ...)	Creates a list out of the given values. For an example, see the Using lists in table calculations Community topic.
lookup	lookup(value, lookup_column, result_column)	Returns the value in result_column that is in the same row as value is in lookup_column.
offset	offset(column, row_offset)	Returns the value of row (n + row_offset) in column, where n is the current row number.For examples using offset, see the Calculating Percent of Previous and Percent Change with Table Calculations and Using offset and pivot_offset in Table Calculations Help Center articles.
offset_list	offset_list(column, row_offset,num_values)	Returns a list of the num_values values starting at row (n + row_offset) in column, where n is the current row number. For an example, see the Calculating Moving Averages Help Center article.
row	row ()	Returns the current row number. For an example, see the Transpose Table (Display Measures as Rows) Help Center article.

Pivot-Related Functions for Table Calculations Only

Some of these functions use the relative positions of pivot columns, so changing the sort order of the pivoted dimension affects the results of those functions.

FUNCTION	SYNTAX	PURPOSE
pivot_column	pivot_column ()	Returns the index of the current pivot column.
pivot_index	pivot_index (expression, pivot_index)	Evaluates expression in the context of the pivot column at position pivot_index (1 for first pivot, 2 second pivot, etc.). Returns null for unpivoted results. For examples using pivot_index, see the Using pivot_index in table calculations and Creating a percent of total across rows with table calculations Community topics.
pivot_offset	pivot_offset(pivot_expression, col_offset)	Returns the value of the pivot_expression in position (n + col_offset), where n is the current pivot column position. Returns null for unpivoted results. For examples using pivot_offset, see the Creating a running total across rows with table calculations Community topic and the Calculating Percent of Previous and Percent Change with Table Calculations and Using offset and pivot_offset in Table Calculations Help Center articles.
pivot_offset_list	pivot_offset_list (pivot_expression,col_offset, num_values)	Returns a list of the the num_values values in pivot_expression starting at position (n + col_offset), where n is the current pivot index. Returns null for unpivoted results.
pivot_row	pivot_row(expression)	Returns the pivoted values of expression as a list. Returns null for unpivoted results. For examples using pivot_row, see the Aggregating Across Rows (Row Totals) in Table Calculations and How to Calculate Percent-of-Total Help Center articles.
pivot_where	pivot_where(select_expression, expression)	Returns the value of expression for the pivot column that uniquely satisfies select_expression or null if such a unique column does not exist.

The specific pivot functions you use determine whether the table calculation is displayed next to each pivoted column, or is displayed as a single column at the end of the table.

Filter Functions for Custom Filters and Custom Fields

Filter functions let you work with filter expressions to return values based on filtered data. Filter functions work in custom filters, filters on custom measures, and custom dimensions, but are not valid in table calculations.

FUNCTION	SYNTAX	PURPOSE
matches_filter	matches_filter (field, `filter_expression`)	Returns Yes if the value of the field matches the filter expression, No if not.

In this section:

Netskope Help

Functions and Operators

Search results