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