Looker Studio provides a number of powerful functions that can be used inside of calculated field formulas.
| Name | Type | Description | Syntax |
|---|---|---|---|
ABS |
Arithmetic | Returns the absolute value of number. Learn more. | ABS(X) |
ACOS |
Arithmetic | Returns the inverse of the cosine of X. Learn more. | ACOS(X) |
APPROX_COUNT_DISTINCT |
Aggregation | Returns the approximate number of unique values of X. Learn more | APPROX_COUNT_DISTINCT(X) |
ASIN |
Arithmetic | Returns the inverse of the sine of X. Learn more. | ASIN(X) |
ATAN |
Arithmetic | Returns the inverse of the tangent of X. Learn more. | ATAN(X) |
AVG |
Aggregation | Returns the average of all values of X. Learn more. | AVG(X) |
CASE (Simple) |
Conditional | Compares input_expression to expression_to_match of each successive WHEN clause and returns the first result where this comparison returns true. Learn more |
|
CASE |
Conditional | Evaluates the condition of each successive WHEN clause and returns the first result where the condition is true; any remaining WHEN and ELSE clauses are not evaluated. If all conditions are false or NULL, returns else_result if present; if not present, returns NULL. Learn more |
|
CAST |
Miscellaneous | Cast field or expression into TYPE. Aggregated fields are not allowed inside CAST.TYPE can be NUMBER, TEXT, or DATETIME. Learn more. |
CAST(field_expression AS TYPE) |
CEIL |
Arithmetic | Returns the nearest integer greater than X. For example, if the value of X is v, CEIL(X) is greater than or equal to v. Learn more. | CEIL(X) |
COALESCE |
Conditional | Returns the first non-missing value found in a list of fields. Learn more. | COALESCE(field_expression[,field_expression, ...]) |
CONCAT |
Text | Returns a text that is the concatenation of X and Y. Learn more. | CONCAT(X, Y) |
CONTAINS_TEXT |
Text | Returns true if X contains text, otherwise returns false. Case-sensitive. Learn more. | CONTAINS_TEXT(X, text) |
COS |
Arithmetic | Returns the cosine of X. Learn more. | COS(X) |
COUNT |
Aggregation | Returns the number of values of X. Learn more. | COUNT(X) |
COUNT_DISTINCT |
Aggregation | Returns the number of unique values of X. Learn more. | COUNT_DISTINCT(X) |
CURRENT_DATE |
Date | Returns the current date as of the specified or default timezone. Learn more. | CURRENT_DATE([time_zone]) |
CURRENT_DATETIME |
Date | Returns the current date and time as of the specified or default timezone. Learn more. | CURRENT_DATETIME([time_zone]) |
DATE |
Date | Constructs a Date field or value from numbers or from a Date & Time field or expression. Learn more. | DATE(year, month, day) |
DATE_DIFF |
Date | Returns the difference in days between X and Y (X - Y). Learn more. | DATE_DIFF(X, Y) |
DATE_FROM_UNIX_DATE |
Date | Interprets an integer as the number of days since 1970-01-01. Learn more. | DATE_FROM_UNIX_DATE(integer) |
DATETIME |
Date | Constructs a Date & Time field or value from numbers. Learn more. | DATETIME(year, month, day, hour, minute, second) |
DATETIME_ADD |
Date | Adds a specified time interval to a date. Learn more. | DATETIME_ADD(datetime_expression, INTERVAL integer part) |
DATETIME_DIFF |
Date | Returns the number of part boundaries between two dates. Learn more. | DATETIME_DIFF(date_expression, date_expression, part) |
DATETIME_SUB |
Date | Subtracts a specified time interval from a date. Learn more. | DATETIME_SUB(datetime_expression, INTERVAL integer part)
|
DATETIME_TRUNC |
Date | Truncates a date to the specified granularity. Learn more. | DATETIME_TRUNC(date_expression, part) |
DAY |
Date | Returns the day of a Date or Date & Time. Learn more. | Day(date_expression) |
ENDS_WITH |
Text | Returns true if X ends with text, otherwise returns false. Case-sensitive. Learn more. | ENDS_WITH(X, text) |
EXTRACT |
Date | Returns part of a Date or Date & Time. Learn more. | EXTRACT(part FROM date_expression) |
FLOOR |
Arithmetic | Returns the nearest integer less than X. For example, if the value X is v, FLOOR(X) is equal to or less than v. Learn more. | FLOOR(X) |
FORMAT_DATETIME |
Date | Returns a formatted date string. Learn more. | FORMAT_DATETIME(format_string, datetime_expression) |
HOUR |
Date | Returns the hour of a date and time. Learn more. | HOUR(datetime_expression) |
HYPERLINK |
Miscellaneous | Returns a hyperlink to the URL, labeled with the link label. Learn more. | HYPERLINK(URL, link label) |
IF |
Conditional | If condition is true, returns true_result, else returns false_result. false_result is not evaluated if condition is true. true_result is not evaluated if condition is false or NULL. Learn more |
IF(condition, true_result, false_result) |
IFNULL |
Conditional | Returns a result if the input is null, otherwise, returns the input. Learn more. | IFNULL(input_expression, null_result) |
IMAGE |
Miscellaneous | Creates Image fields in your data source Learn more. | IMAGE(Image URL, [Alternative Text]) |
LEFT_TEXT |
Text | Returns a number of characters from the beginning of X. The number of characters is specified by length. Learn more. | LEFT_TEXT(X, length) |
LENGTH |
Text | Returns the number of characters in X. Learn more. | LENGTH(X) |
LOG |
Arithmetic | Returns the logarithm to base 2 of X. Learn more. | LOG(X) |
LOG10 |
Arithmetic | Returns the logarithm to base 10 of X. Learn more. | LOG10(X) |
LOWER |
Text | Converts X to lowercase. Learn more. | LOWER(X) |
MAX |
Aggregation | Returns the maximum value of X. Learn more. | MAX(X) |
MEDIAN |
Aggregation | Returns the median of all values of X. Learn more. | MEDIAN(X) |
MIN |
Aggregation | Returns the minimum value of X. Learn more. | MIN(X) |
MINUTE |
Date | Returns the minutes component of a given date and time. Learn more. | MINUTE(datetime_expression) |
MONTH |
Date | Returns the month from a Date & Time value. Learn more. | MONTH(date_expression) |
NARY_MAX |
Arithmetic | Returns the maximum value of X, Y, [,Z]*. All input arguments must be of the same type: all numbers. At least one input argument must be a field or an expression containing a field. Learn more. | NARY_MAX(X, Y [,Z]*) |
NARY_MIN |
Arithmetic | Returns the minimum value of X, Y, [,Z]*. All input arguments must be of the same type, all numbers. At least one input argument must be a field or an expression containing a field. Learn more. | NARY_MIN(X, Y [,Z]*) |
NATIVE_DIMENSION |
Miscellaneous | Returns the result of a SQL expression as evaluated by the underlying dataset. The expression cannot include any aggregations. Learn more. | NATIVE_DIMENSION("JSON_VALUE('{"name": "Dana"}', '$.name')","STRING") |
NULLIF |
Conditional | Returns null if the input matches an expression, otherwise returns the input. Learn more. | NULLIF(input_expression, expression_to_match) |
PARSE_DATE |
Date | Converts text to a date. Learn more. | PARSE_DATE(format_string, text) |
PARSE_DATETIME |
Date | Converts text to a date with time. Learn more. | PARSE_DATETIME(format_string, text) |
PERCENTILE |
Aggregation | Returns the percentile rank N of field X. Learn more. | PERCENTILE(X,N) |
POWER |
Arithmetic | Returns result of raising X to the power Y. Learn more. | POWER(X, Y) |
QUARTER |
Date | Returns the quarter of the year for a given date. Learn more. | QUARTER(date_expression) |
REGEXP_CONTAINS |
Text | Returns true if X contains the regular expression pattern, otherwise returns false. Learn more. | REGEXP_CONTAINS(X, regular_expression) |
REGEXP_EXTRACT |
Text | Returns first matching substring in X which matches the regular expression pattern. Learn more. | REGEXP_EXTRACT(X, regular_expression) |
REGEXP_MATCH |
Text | Returns true if X matches the regular expression pattern, otherwise returns false. Learn more. | REGEXP_MATCH(X, regular_expression) |
REGEXP_REPLACE |
Text | Replaces all occurrences of text which matches the regular expression pattern in X with the replacement string. Learn more. | REGEXP_REPLACE(X, regular_expression, replacement) |
REPLACE |
Text | Returns a copy of X with all occurrences of Y in X replaced by Z. Learn more. | REPLACE(X, Y, Z) |
RIGHT_TEXT |
Text | Returns a number of characters from the end of X. The number of characters is specified by length. Learn more. | RIGHT_TEXT(X, length) |
ROUND |
Arithmetic | Returns X rounded to Y precision digits. Learn more. | ROUND(X, Y) |
SECOND |
Date | Returns the seconds component of a given date and time. Learn more. | SECOND(datetime_expression) |
SIN |
Arithmetic | Returns the sine of X. Learn more. | SIN(X) |
SQRT |
Arithmetic | Returns the square root of X. Note that X must be non-negative. Learn more. | SQRT(X) |
STARTS_WITH |
Text | Returns true if X starts with text. Otherwise, returns false. Case-sensitive. Learn more. | STARTS_WITH(X, text) |
STDDEV |
Aggregation | Returns the standard deviation of X. Learn more. | STDDEV(X) |
SUBSTR |
Text | Returns a text that is a substring of X. The substring begins at start index and is length characters long. Learn more. | SUBSTR(X, start index, length) |
SUM |
Aggregation | Returns the sum of all values of X. Learn more. | SUM(X) |
TAN |
Arithmetic | Returns the tangent of X. Learn more. | TAN(X) |
TOCITY |
Geo | Returns the city name for X. | TOCITY(X [,Input Format]) |
TOCONTINENT |
Geo | Returns the continent name for X. Learn more. | TOCONTINENT(X [,Input Format]) |
TOCOUNTRY |
Geo | Returns the country name for X. Learn more. | TOCOUNTRY(X [,Input Format]) |
TODATE |
Date | Returns a formatted compatibility mode Date. Learn more. | TODATE(X, Input Format, Output Format) |
TODAY |
Date | Returns the current date as of the specified or default timezone. Learn more. | TODAY([time_zone]) |
TOREGION |
Geo | Returns the region name for X. Learn more. | TOREGION(X [,Input Format]) |
TOSUBCONTINENT |
Geo | Returns the sub-continent name for X. Learn more. | TOSUBCONTINENT(X [,Input Format]) |
TRIM |
Text | Returns X with leading and trailing spaces removed. Learn more. | TRIM(X) |
UNIX_DATE |
Date | Returns the number of days since 1970-01-01. Learn more. | UNIX_DATE(date_expression) |
UPPER |
Text | Converts X to uppercase. Learn more. | UPPER(X) |
VARIANCE |
Aggregation | Returns the variance of X. Learn more. | VARIANCE(X) |
WEEK |
Date | Returns the week number for a given date. Learn more. | WEEK(Date) |
WEEKDAY |
Date | Returns a number representing the day of the week for a given date. Learn more. | WEEKDAY(Date) |
YEAR |
Date | Returns the year of a given date. Learn more. | YEAR(Date) |
YEARWEEK |
Date | Returns the year and week number of a given date. Learn more. | YEARWEEK(Date) |