# Functions and Operators

##### 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

Functions for Table Calculations Only

Warning

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

Operators for Any Expression

You can use the following standard mathematical operators:

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

Functions for Table Calculations Only

Date Functions

Date functions enable you to work with dates and times.

Functions for Any Expression

Functions for Table Calculations Only

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

Operators for Any Expression

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

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

You also can combine expressions with these logical operators:

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:

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:

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.

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.

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.