Looker Functions and Operators

On this Page
Docs Menu

Table Calculations and Custom Filters both use Looker Expressions. A major part of these expressions are the functions and operators that you can use in them. This page includes information about all of these functions and operators.

The functions and operators can be divided into a few basic categories:

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, taking a square root, 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 performed 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 Custom Filters and Table Calculations

Function Syntax Purpose Relevant Discourse Articles
abs abs(value) Returns the absolute value of value Example
ceiling ceiling(value) Returns the smallest integer greater than or equal to value
exp exp(value) Returns e to the power of value Example
floor floor(value) Returns the largest integer less than or equal to value
ln ln(value) Returns the natural logarithm of value Example
log log(value) Returns the base 10 logarithm of value
mod mod(value, divisor) Returns the remainder of dividing value by divisor
power power(base, exponent) Returns base raised to the power of exponent Example
rand rand() Returns a random number between 0 and 1 Example
round round(value, num_decimals) Returns value rounded to num_decimals decimal places Example 1
Example 2
sqrt sqrt(value) Returns the square root of value Example

Functions for Table Calculations Only

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

Function Syntax Purpose Relevant Discourse Articles
count count(value_column) Returns the count of non-null values in value_column
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 Example 1
Example 2
Example 3
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 Example 1
Example 2
median median(expression) Returns the median of the column created by expression unless expression defines a column of Lists, in which case returns the median of each List
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
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. Note: percentile_value must be between 0 and 1, else this returns null
running_product running_product(value_column) Returns a running product of the values in value_column
running_total running_total(value_column) Returns a running total of the values in value_column Example 1
Example 2
stddev_pop stddev_pop(expression) Returns the standard deviation (population) of the column created by expression unless expression defines a column of Lists, in which case returns the standard deviation (population) of each List
stddev_samp stddev_samp(expression) Returns the standard deviation (sample) of the column created by expression unless expression defines a column of Lists, in which case returns the standard deviation (sample) of each List
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 Example 1
Example 2
Example 3
Example 4
var_pop var_pop(expression) Returns the variance (population) of the column created by expression unless expression defines a column of Lists, in which case returns the variance (population) of each List
var_samp var_pop(expression) Returns the variance (sample) of the column created by expression unless expression defines a column of Lists, in which case returns the variance (sample) of each List

Operators for Custom Filters and Table Calculations

You can use the following standard mathematical operators:

Operators Syntax Purpose
+ value_1 + value_2 Adds value_1 and value_2
- value_1 - value_2 Subtracts value_1 and value_2
* value_1 * value_2 Multiplies value_1 and value_2
/ value_1 / value_2 Divides value_1 and value_2

String Functions

String functions operate on sentences, words, or letters, which are collectively called “strings”. String functions are used 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. They can also be used to format the data returned in the table.

Functions for Custom Filters and Table Calculations

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 lower case
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 upper case

Date Functions

Date functions enable you to work with dates and times.

Functions for Custom Filters and Table Calculations

Function Syntax Purpose Relevant Discourse Articles
add_days add_days(number, date) Adds number days to date
add_hours add_hours(number, date) Adds number hours to date
add_minutes add_minutes(number, date) Adds number minutes to date
add_months add_months(number, date) Adds number months to date
add_seconds add_seconds(number, date) Adds number seconds to date
add_years add_years(number, date) Adds number years 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 Example
diff_hours diff_hours(start_date, end_date) Returns the number of hours between start_date and end_date
diff_minutes diff_minutes(start_date, end_date) Returns the number of minutes between start_date and end_date Example
diff_months diff_months(start_date, end_date) Returns the number of months between start_date and end_date Example
diff_seconds diff_seconds(start_date, end_date) Returns the number of seconds between start_date and end_date
diff_years diff_years(start_date, end_date) Returns the number of years between start_date and end_date
extract_days extract_days(date) Extracts the days from date Example
extract_hours extract_hours(date) Extracts the hours from date
extract_minutes extract_minutes(date) Extracts the minutes from date
extract_months extract_months(date) Extracts the months from date
extract_seconds extract_seconds(date) Extracts the seconds from date
extract_years extract_years(date) Extracts the years from date
now now() Returns the current date and time Example 1
Example 2
trunc_days trunc_days(date) Truncates date to days
trunc_hours trunc_hours(date) Truncates date to hours
trunc_minutes trunc_minutes(date) Truncates date to minutes
trunc_months trunc_months(date) Truncates date to months
trunc_years trunc_years(date) Truncates date to years

Additional information and examples can be found in this Discourse article.

Logical Functions, Operators, and Constants

Logical functions and operators deal with whether or not something is true or false. This type of function takes the value of something, evaluates it against some criteria, returns true if the criteria is met, and false if the criteria is not met. There are also various logical operators for comparing values and combining logical expressions.

Functions for Custom Filters and Table Calculations

Function Syntax Purpose Relevant Discourse Articles
coalesce coalesce(value_1, value_2, ...) Returns the first non-null value in value_1, value_2, ..., value_n if found and null otherwise Example 1
Example 2
Example 3
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 Example 1
Example 2
is_null is_null(value) Returns Yes if value is null, and No otherwise Example 1
Example 2

Operators for Custom Filters and Table Calculations

The following comparison operators can be used with any datatype:

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

Logical Constants

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

Constant Meaning
yes True
no False
null There is no value

Note that the constants yes and no, are the special symbols that ‚Äčmean true or false in Looker 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. Thus the following expression without additional parentheses:

if (
  ${order_items.days_to_process}>=4 OR
  ${order_items.shipping_time}>5 AND
  ${order_facts.is_first_purchase},
"review", "okay")

would be evaluated as:

if (
  ${order_items.days_to_process}>=4 OR
  (${order_items.shipping_time}>5 AND ${order_facts.is_first_purchase}),
"review", "okay")

Positional Functions

When creating table calculations, you can use positional transformation functions to extract information about fields in different rows or pivot columns.

Row-related Functions for Table Calculations Only

Function Syntax Purpose Relevant Discourse Articles
list list(value_1, value_2, ...) Creates a List out of the given values Example
offset offset(column, row_offset) Returns the value of row (n + row_offset) in column, where n is the current row number Example 1
Example 2
Example 3
Example 4
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 Example 1
Example 2
row row() Returns the current row number Example

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 Relevant Discourse Articles
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 Example 1
Example 2
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 Example 1
Example 2
Example 3
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 Example
pivot_row pivot_row(expression) Returns the pivoted values of expression as a List. Returns null for unpivoted results. Example 1
Example 2
pivot_where pivot_where(select_expression, expression) Returns the value of expression for the pivot column which uniquely satisfies select_expression or null if such a unique column does not exist.

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

Still have questions?
Go to Discourse - or - Email Support
Top