Looker Functions and Operators

• Docs Home
• Start
• Browse
• Getting Started
• Latest Info
• Browsing and Sharing Tutorial
• Dashboards
• Reference
• Explore
• Getting Started
• Latest Info
• Exploring Tutorial
• Charts
• Dashboards
• Reference
• Develop
• Getting Started
• Writing LookML
• Looker and SQL
• LookML Reference
• LookML Dashboards
• Looker API
• Release Notes
• Getting Started
• Tutorials
• General Pages
• Users Pages
• Database Pages
• Scheduler Pages
• Integrations Pages
• Authentication Pages
• Server Pages
• On-premise Management
• Version Support
• Release Notes
• Setup
• Getting Started
• Tutorials
• Looker-hosted Installation
• On-premise Installation
• Getting Database Access
• Enabling User Access

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.

- or -
Top