Netskope Help

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.

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.

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} &gt; 100, yes, no)

is equivalent to this:

${field} &gt; 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} &lt; 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.