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  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
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 nonnull 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_pop(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
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 “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 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 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 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:
You also can combine Looker Expressions with these logical operators:
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.
Rowrelated 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 
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 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 
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 1 Example 2 Example 3 
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.