Looker expressions (sometimes referred to as “lexp”) 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. This page includes information about all of these functions and operators.
The functions and operators can be divided into a few basic categories:
 Mathematical: Numberrelated functions
 String: Word and letterrelated functions
 Dates: Date and timerelated 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
Looker expressions for custom filters and custom fields do not support Looker 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 Looker 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, 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 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 Looker Expression
Function  Syntax  Purpose  Relevant Community Topics 

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 

floor  floor(value) 
Returns the largest integer less than or equal to value 

ln  ln(value) 
Returns the natural logarithm of value 

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  
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
Many of these functions operate over many rows and will only consider the rows returned by your query.
Function  Syntax  Purpose  Relevant Community Topics 

acos  acos(value) 
Returns the inverse cosine of value 

asin  asin(value) 
Returns the inverse sine of value 

atan  atan(value) 
Returns the inverse tangent of value 

beta_dist  beta_dist(value, alpha, beta, cumulative) 
Returns the position of value on the beta distribution with parameters alpha and beta . If cumulative = yes , returns the cumulative probability 

beta_inv  beta_inv(probability, alpha, beta) 
Returns the position of probability on the inverse cumulative beta distribution with parameters alpha and beta 

binom_dist  binom_dist(num_successes, num_tests, probability, cumulative) 
Returns the probability of getting num_successes successes in num_tests tests with the given probability of success. If cumulative = yes , returns the cumulative probability 

binom_inv  binom_inv(num_tests, test_probability, target_probability) 
Returns the smallest number k such that binom(k, num_tests, test_probability, yes) >= target_probability 

chisq_dist  chisq_dist(value, dof, cumulative) 
Returns the position of value on the gamma distribution with dof degrees of freedom. If cumulative = yes , returns the cumulative probability 

chisq_inv  chisq_inv(probability, dof) 
Returns the position of probability on the inverse cumulative gamma distribution with dof degrees of freedom 

chisq_test  chisq_test(actual, expected) 
Returns the probability for the chisquared test for independence between actual and expected data. actual can be a column or a column of lists, and expected must be the same type. 

combin  combin(set_size, selection_size) 
Returns the number of ways of choosing selection_size elements from a set of size set_size 

confidence_norm  confidence_norm(alpha, stdev, n) 
Returns half the width of the normal confidence interval at significance level alpha , standard deviation stdev , and sample size n 

confidence_t  confidence_t(alpha, stdev, n) 
Returns half the width of the Student’s tdistribution confidence interval at significance level alpha , standard deviation stdev , and sample size n 

correl  correl(column_1, column_2) 
Returns the correlation coefficient of column_1 and column_2 

cos  cos(value) 
Returns the cosine of value 

count  count(expression) 
Returns the count of nonnull 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 nonnull values in the column defined by expression , unless expression defines a column of Lists, in which case returns the count in each List 

covar_pop  covar_pop(column_1, column_2) 
Returns the population covariance of column_1 and column_2 

covar_samp  covar_samp(column_1, column_2) 
Returns the sample covariance of column_1 and column_2 

degrees  degrees(value) 
Converts value from radians to degrees 

expon_dist  expon_dist(value, lambda, cumulative) 
Returns the position of value on the exponential distribution with parameter lambda . If cumulative = yes , returns the cumulative probability 

f_dist  f_dist(value, dof_1, dof_2, cumulative) 
Returns the position of value on the F distribution with parameters dof_1 and dof_2 . If cumulative = yes , returns the cumulative probability 

f_inv  f_inv(probability, dof_1, dof_2) 
Returns the position of probability on the inverse cumulative F distribution with parameters dof_1 and dof_2 

fact  fact(value) 
Returns the factorial of value 

gamma_dist  gamma_dist(value, alpha, beta, cumulative) 
Returns the position of value on the gamma distribution with parameters alpha and beta . If cumulative = yes , returns the cumulative probability 

gamma_inv  gamma_inv(probability, alpha, beta) 
Returns the position of probability on the inverse cumulative gamma distribution with parameters alpha and beta 

geomean  geomean(expression) 
Returns the geometric mean of the column created by expression unless expression defines a column of Lists, in which case returns the geometric mean of each List 

hypgeom_dist  hypgeom_dist (sample_successes, sample_size, population_successes, population_size, cumulative) 
Returns the probability of getting sample_successes from the given sample_size , number of population_successes , and population_size . If cumulative = yes , returns the cumulative probability 

intercept  intercept(y_column, x_column) 
Returns the intercept of the linear regression line through the points determined by y_column and x_column 
Example 
kurtosis  kurtosis(expression) 
Returns the sample excess kurtosis of the column created by expression unless expression defines a column of Lists, in which case returns the sample excess kurtosis of each List 

large  large(expression, k) 
Returns the k th largest value of the column created by expression unless expression defines a column of Lists, in which case returnsthe k th largest value of each List 

match  match(value, expression) 
Returns the row number of the first occurence of value in the column created by expression unless expression defines a column of Lists, in which case returns the position of value 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 
Example 1 Example 2 
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 

mode  mode(expression) 
Returns the mode of the column created by expression unless expression defines a column of Lists, in which case returns the mode of each List 

multinomial  multinomial(value_1, value_2, ...) 
Returns the factorial of the sum of the arguments divided by the product of each of their factorials  
negbinom_dist  negbinom_dist(num_failures, num_successes, probability, cumulative) 
Returns the probability of getting num_failures failures before getting num_successes successes, with the given probability of success. If cumulative = yes , returns the cumulative probability 

norm_dist  norm_dist(value, mean, stdev, cumulative) 
Returns the position of value on the normal distribution with the given mean and stdev . If cumulative = yes , then returns the cumulative probability 

norm_inv  norm_inv(probability, mean, stdev) 
Returns the position of probability on the inverse normal cumulative distribution 

norm_s_dist  norm_s_dist(value, cumulative) 
Returns the position of value on the standard normal distribution. If cumulative = yes , returns the cumulative probability 

norm_s_inv  norm_s_inv(probability) 
Returns the position of probability on the inverse standard normal cumulative distribution 

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. Note: percentile_value must be between 0 and 1, else this returns null 

pi  pi() 
Returns the value of pi  
poisson_dist  poisson_dist(value, lambda, cumulative) 
Returns the position of value on the poisson distribution with parameter lambda . If cumulative = yes , returns the cumulative probability 

product  product(expression) 
Returns the product of the column created by expression unless expression defines a column of Lists, in which case returns the product of each List 

radians  radians(value) 
Converts value from degrees to radians 

rank  rank(value, expression) 
Returns the rank of value in the column created by expression . For example, if you want to rank orders by their total sale price, you could use rank(${order_items.total_sale_price},${order_items.total_sale_price}) , which gives a rank for each value of order_items.total_sale_price in your query when comparing it to the entire column of order_items.total_sale_price in your query. In the case where the expression defines multiple lists, this function returns the relative size of the value in each list. 
Example 
rank_avg  rank_avg(value, expression) 
Returns the average rank of value in the column created by expression unless expression defines a column of lists, in which case returns the average rank of value in each list. 

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 
sin  sin(value) 
Returns the sine of value 

skew  skew(expression) 
Returns the sample skewness of the column created by expression unless expression defines a column of Lists, in which case returns the sample skewness of each List 

slope  slope(y_column, x_column) 
Returns the slope of the linear regression line through points determined by y_column and x_column 
Example 
small  small(expression, k) 
Returns the k th smallest value of the column created by expression unless expression defines a column of Lists, in which case returnsthe k th smallest value of each List 

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 
t_dist  t_dist(value, dof, cumulative) 
Returns the position of value on the Student’s tdistribution with dof degrees of freedeom. If cumulative = yes , returns the cumulative probability 

t_inv  t_inv(probability, dof) 
Returns the position of probability on the inverse normal cumulative distribution with dof degrees of freedom 

t_test  t_test(column_1, column_2, tails, type) 
Returns the result of a Student’s ttest on the data from column_1 and column_2 , using 1 or 2 tails . type : 1 = paired, 2 = homoscedastic, 3 = heteroscedastic 

tan  tan(value) 
Returns the tangent of value 

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 

weibull_dist  weibull_dist(value, shape, scale, cumulative) 
Returns the position of value on the Weibull distribution with parameters shape and scale . If cumulative = yes , returns the cumulative probability 

z_test  z_test(data, value, stdev) 
Returns the onetailed pvalue of the ztest using the existing data and stdev on the hypothesized mean value . 
Operators for Any Looker 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. They can also be used to format the data returned in the table.
Functions for Any Looker Expression
Functions for Table Calculations Only
Date Functions
Date functions enable you to work with dates and times.
Functions for Any Looker Expression
Function  Syntax  Purpose  Relevant Community Topics 

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 “yearmonthday ” date or null if the date would be invalid 

date_time  date_time(year, month, day, hours, minutes, seconds) 
Returns “ yearmonthday 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 Community topic.
Functions for Table Calculations Only
Function  Syntax  Purpose 

to_date  to_date(string) 
Returns the date and time corresponding to string (YYYY, YYYYMM, YYYYMMDD, YYYYMMDD hh, YYYYMMDD hh:mm, or YYYYMMDD 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 Looker Expression
Function  Syntax  Purpose  Relevant Community Topics 

coalesce  coalesce(value_1, value_2, ...) 
Returns the first nonnull 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 
is_null  is_null(value) 
Returns Yes if value is null , and No otherwise 
Example 1 Example 2 
Operators for Any Looker 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:
You also can combine Looker expressions with these logical operators:
These logical operators must be capitalized. Logical operators written in lowercase will not work.
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. 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 
RowRelated 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  Relevant Community Topics 

index  index(expression, n) 
Returns the value of the n th element of the column created by expression , unless expression defines a column of Lists, in which case returns the n th element of each list 

list  list(value_1, value_2, ...) 
Creates a List out of the given values  Example 
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 
Example 1 Example 2 Example 3 
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 
PivotRelated 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 Community Topics 

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 

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 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`) 
ADDED5.16 Returns Yes if the value of the field matches the filter expression, No if not. 