Custom calculation function reference

This page provides a complete reference of all supported functions for custom calculations in AI/BI dashboards. For information about how to use custom calculations, see What are custom calculations?.

Aggregate functions

All calculated measures must be aggregated. The following aggregation operations are supported:

note

Use the DISTINCT keyword in aggregate functions to include only unique values in the aggregation. Additionally, the FILTER(WHERE condition) clause can be appended to any aggregate function to limit the values that are included in the calculation.

Function	Description
any(expr)	Returns true if at least one value of expr in the group is true.
any_value(expr)	Returns some value of expr for a group of rows.
approx_count_distinct(expr[, relativeSD])	Returns the estimated number of distinct values in expr.
approx_percentile ([ALL | DISTINCT] expr, percentile [, accuracy])	Returns the approximate percentile value of expr at the specified percentile.
avg(expr)	Returns the calculated mean in a column or expression.
bool_or(expr)	Returns true if at least one value of expr is true.
corr(expr1, expr2)	Returns the Pearson correlation coefficient between expr1 and expr2.
count(*)	Returns the number of rows in a group.
count(DISTINCT expr)	Returns the number of unique rows in a group.
count_if(expr)	Returns the count of rows that satisfy a given condition.
first(expr [, ignoreNull])	Returns the first value of expr for a group.
first_value(expr [, ignoreNull])	Returns the first value of expr for a group.
last(expr [, ignoreNull])	Returns the last value of expr for the group.
last_value(expr [, ignoreNull])	Returns the last value of expr for the group.
listagg(expr [, delimiter])	Returns the concatenation of non-null values in the group.
max(expr)	Returns the maximum value in a column or expression.
max_by(expr1, expr2)	Returns the value of expr1 associated with the maximum value of expr2.
mean(expr)	Returns the calculated mean in a column or expression.
median(expr)	Returns the median of a set of values.
min(expr)	Returns the minimum value in a column or expression.
min_by(expr1, expr2)	Returns the value of expr1 associated with the minimum value of expr2.
mode(expr [, deterministic ])	Returns the most frequent value for expr.
percentile(expr, percentage [, frequency])	Returns the exact percentile value of expr at the specified percentile in a group.
percentile_approx(expr, percentage [, accuracy])	Returns the approximate percentile value of expr at the specified percentile.
regr_slope(y, x)	Returns the slope of the linear regression line for non-null pairs in a group.
some(expr)	Returns true if at least one value of expr in the group is true.
std(expr)	Returns the standard deviation of a set of values.
stddev(expr)	Returns the standard deviation of a set of values.
stddev_pop(expr)	Returns the population standard deviation of a set of values.
stddev_samp(expr)	Returns the sample standard deviation of a set of values.
string_agg(expr [, delimiter])	Returns the concatenation of non-null string values in the group.
sum(expr)	Returns the total of values in a column or expression.
variance(expr)	Returns the variance of a set of values.

Arithmetic operations

You can combine expressions with the following arithmetic operations:

Operation	Description
expr1 % expr2	Returns the remainder of dividing expr1 by expr2.
multiplier * multiplicand	Returns the product of two expressions.
expr1 + expr2	Returns the sum of expr1 and expr2.
+ expr	Returns the value of the expression.
expr1 - expr2	Returns the difference when subtracting expr2 from expr1.
- expr	Returns the negated value of the expression.
dividend / divisor	Returns the result of dividing the dividend by the divisor.
dividend div divisor	Returns the integral part of the division of dividend by divisor.
abs(expr)	Returns the absolute value of the numeric expression.
acos(expr)	Returns the inverse cosine (arc cosine) of expr.
asin(expr)	Returns the inverse sine (arc sine) of expr.
bround(expr [, d])	Returns expr rounded to d decimal places using HALF_EVEN rounding mode.
ceil(expr) or ceiling(expr)	Returns the smallest integer not smaller than expr.
cos(expr)	Returns the cosine of expr.
exp(expr)	Returns e raised to the power of expr.
floor(expr)	Returns the largest integer not greater than expr.
ln(expr)	Returns the natural logarithm of the expression.
log(base, expr)	Returns the logarithm of expr with the specified base.
log10(expr)	Returns the base-10 logarithm of the expression.
mod(expr1, expr2)	Returns the remainder of dividing expr1 by expr2.
nullifzero(expr)	Returns NULL if expr is 0, otherwise returns expr.
pi()	Returns the value of pi.
pmod(expr1, expr2)	Returns the positive value of expr1 mod expr2.
pow(expr1, expr2) or power(expr1, expr2)	Returns the result of expr1 raised to the power of expr2.
radians(expr)	Converts degrees to radians.
rand([seed])	Returns a random value with uniform distribution in the range from 0 (inclusive) to 1 (exclusive).
round(expr [, d])	Returns expr rounded to d decimal places using HALF_UP rounding mode.
sign(expr)	Returns the sign of the numeric expression.
sin(expr)	Returns the sine of expr.
sqrt(expr)	Returns the square root of expr.
try_add(expr1, expr2)	Adds two values. If an error occurs, returns NULL.
try_divide(dividend, divisor)	Divides the dividend by the divisor. If an error occurs, returns NULL.
try_multiply(multiplier, multiplicand)	Multiplies two numbers. If an error occurs, returns NULL.
try_subtract(expr1, expr2)	Subtracts expr2 from expr1. If an error occurs, returns NULL.
zeroifnull(expr)	Returns 0 if expr is NULL, otherwise returns expr.

Boolean functions and operators

Custom calculations support basic comparison and Boolean operators. The following operators and functions are supported:

Operation	Description
expr1 != expr2	Returns true if expr1 is not equal to expr2.
!expr	Logical not.
expr1 & expr2	Returns the bitwise AND of expr1 and expr2.
expr1 && expr2	Returns true if both expr1 and expr2 are true.
expr1 <=> expr2	Returns same result as the equal operator for non-null operands, but returns true if both are null, and false if one is null.
expr1 <> expr2	Returns true if expr1 is not equal to expr2.
expr1 < expr2	Returns true if expr1 is less than expr2.
expr1 <= expr2	Returns true if expr1 is less than or equal to expr2.
expr1 = expr2	Returns true if expr1 equals expr2.
expr1 == expr2	Returns true if expr1 equals expr2.
expr1 > expr2	Returns true if expr1 is greater than expr2.
expr1 >= expr2	Returns true if expr1 is greater than or equal to expr2.
expr1 ^ expr2	Returns the bitwise exclusive OR of expr1 and expr2.
expr1 | expr2	Returns the bitwise OR of expr1 and expr2.
expr1 || expr2	Returns true if at least one of expr1 or expr2 is true.
~expr	Returns the bitwise NOT of expr.
expr1 and expr2	Returns true if both expr1 and expr2 are true.
expr between lower and upper	Tests whether expr falls within the specified range (special syntax).
str ilike pattern	Returns true if str matches pattern (case-insensitive). Can also be used as ilike(str, pattern [, escapeChar]).
expr in (value1, value2, ...)	Tests whether expr matches any value in a list (special syntax).
isnan(expr)	Returns true if expr is NaN (not a number).
isnotnull(expr)	Returns true if expr is not NULL.
isnull(expr)	Returns true if expr is NULL.
str like pattern	Returns true if str matches pattern. Can also be used as like(str, pattern [, escapeChar]).
not expr	Logical not (prefix operator). Can also be used as not(expr).
expr1 or expr2	Returns true if at least one of expr1 or expr2 is true.
str regexp regexp	Returns true if str matches the regular expression regexp.
regexp_like(str, regexp)	Returns true if str matches the regular expression regexp.
str rlike regexp	Returns true if str matches the regular expression regexp.

Cast functions

Use the following functions to cast values to a specified type:

Function	Description
expr :: type	Casts the value expr to the target data type type.
bigint(expr)	Casts the value expr to BIGINT.
boolean(expr)	Casts the value expr to BOOLEAN.
cast(expr AS type)	Casts the value expr to the target data type type.
date(expr)	Casts the value expr to DATE.
decimal(expr [, p [, s]])	Casts the value expr to DECIMAL with precision p and scale s.
double(expr)	Casts the value expr to DOUBLE.
float(expr)	Casts the value expr to FLOAT.
int(expr)	Casts the value expr to INT.
string(expr)	Casts the value expr to STRING.
timestamp(expr)	Casts the value expr to TIMESTAMP.
to_date(expr [, fmt])	Converts the expr with the fmt to a date.
to_timestamp(expr [, fmt])	Converts the expr with the fmt to a timestamp.
try_cast(expr AS type)	Casts the value expr to the target data type type safely.
try_to_timestamp(expr [, fmt])	Parses the expr with the fmt to a timestamp safely.

Date, timestamp, and interval functions

Use the following functions to work with dates, timestamps, and intervals:

Function	Description
add_months(start_date, num_months)	Returns the date that is num_months after start_date.
curdate()	Returns the current date.
current_date()	Returns the current date.
current_timestamp()	Returns the current timestamp.
date_add(start_date, num_days)	Returns the date that is num_days after start_date.
date_diff(endDate, startDate)	Returns the number of days from startDate to endDate.
date_format(expr, fmt)	Converts a timestamp to a string in the format fmt.
date_part(field, source)	Extracts a specific part, such as year, month, or day, from a date or timestamp.
date_sub(start_date, num_days)	Returns the date that is num_days before start_date.
date_trunc(fmt, source)	Truncates a date or timestamp to a specified unit, such as year or month.
dateadd(unit, value, expr)	Adds the specified time interval to a date or timestamp.
datediff(endDate, startDate)	Returns the number of days from startDate to endDate.
day(expr)	Returns the day of the month from the date or timestamp.
dayname(expr)	Returns the name of the day of the week from the date or timestamp.
dayofmonth(expr)	Returns the day of the month from the date or timestamp.
dayofweek(expr)	Returns the day of the week from the date or timestamp (1 = Sunday, 7 = Saturday).
dayofyear(expr)	Returns the day of the year from the date or timestamp.
extract(field FROM source)	Extracts a part of the date or timestamp.
from_unixtime(unix_time [, fmt])	Converts the number of seconds from Unix epoch to a timestamp.
from_utc_timestamp(timestamp, timezone)	Interprets the given timestamp as UTC and converts to the given time zone.
getdate()	Returns the current timestamp.
hour(expr)	Returns the hour component of the timestamp.
last_day(expr)	Returns the last day of the month which the date or timestamp belongs to.
make_date(year, month, day)	Creates a date from year, month, and day fields.
make_timestamp(year, month, day, hour, min, sec [, timezone])	Creates a timestamp from year, month, day, hour, minute, second, and optionally timezone fields.
minute(expr)	Returns the minute component of the timestamp.
month(expr)	Returns the month component of the date or timestamp.
months_between(timestamp1, timestamp2 [, roundOff])	Returns the number of months between timestamp1 and timestamp2.
next_day(start_date, day_of_week)	Returns the first date which is later than start_date and named as day_of_week.
now()	Returns the current timestamp.
quarter(expr)	Returns the quarter of the year for the date or timestamp (1 to 4).
second(expr)	Returns the second component of the timestamp.
timediff(endDate, startDate)	Returns the difference between two timestamps measured in units.
timestamp_micros(microseconds)	Creates a timestamp from the number of microseconds since Unix epoch.
timestamp_millis(milliseconds)	Creates a timestamp from the number of milliseconds since Unix epoch.
timestamp_seconds(seconds)	Creates a timestamp from the number of seconds since Unix epoch.
timestampadd(unit, value, timestamp)	Adds the specified time interval to a timestamp.
timestampdiff(unit, start, end)	Returns the difference between two timestamps measured in units.
to_date(expr [, fmt])	Converts the expr with the fmt to a date.
to_timestamp(expr [, fmt])	Converts the expr with the fmt to a timestamp.
to_unix_timestamp(expr [, fmt])	Returns the Unix timestamp of the given time.
to_utc_timestamp(timestamp, timezone)	Interprets the given timestamp in the given time zone and converts to UTC.
trunc(expr, fmt)	Returns expr with the time portion of the day truncated to the unit specified by the format model fmt.
unix_micros(timestamp)	Returns the number of microseconds since Unix epoch.
unix_millis(timestamp)	Returns the number of milliseconds since Unix epoch.
unix_seconds(timestamp)	Returns the number of seconds since Unix epoch.
unix_timestamp([expr [, fmt]])	Returns the Unix timestamp of the given time.
weekday(expr)	Returns the day of the week from the date or timestamp (0 = Monday, 6 = Sunday).
weekofyear(expr)	Returns the week of the year from the date or timestamp.
year(expr)	Returns the year component of the date or timestamp.

String functions

Use the following functions to transform strings. To transform datetime strings, see Cast functions.

Function	Description
base64(expr)	Converts the argument to a base64 string.
char(expr)	Returns the character at the supplied UTF-16 code point.
charindex(substr, str [, pos])	Returns the position of the first occurrence of substr in str after position pos.
concat(expr1, expr2 [, ...])	Returns the concatenation of the arguments.
concat_ws(sep [, expr1 [, ...]])	Returns the concatenation of strings separated by sep.
contains(left, right)	Returns true if right is found in left.
convert_timezone(sourceTz, targetTz, sourceTs)	Converts the timestamp sourceTs from the sourceTz time zone to targetTz time zone.
decode(expr, charSet)	Translates binary expr to a string using the character set encoding charSet.
endswith(left, right)	Returns true if left ends with right.
find_in_set(str, str_array)	Returns the index (1-based) of the given string in the comma-delimited list.
format_number(expr, scale)	Formats expr like #,###,###.##, rounded to scale decimal places.
format_string(format, expr [, ...])	Returns a formatted string from printf-style format strings.
hex(expr)	Converts expr to hexadecimal.
initcap(str)	Returns str with the first letter of each word in uppercase.
instr(str, substr)	Returns the position of the first occurrence of substr in str.
lcase(str)	Returns str with all characters changed to lowercase.
left(str, len)	Returns the leftmost len characters from the string str.
len(expr)	Returns the length of the string or binary expression.
length(expr)	Returns the length of the string or binary expression.
levenshtein(str1, str2 [, threshold])	Returns the Levenshtein distance between the two given strings.
locate(substr, str [, pos])	Returns the position of the first occurrence of substr in str after position pos.
lower(str)	Returns str with all characters changed to lowercase.
lpad(str, len [, pad])	Returns str left-padded with pad to a length of len.
ltrim(str [, trimStr])	Removes leading characters from str.
md5(expr)	Returns an MD5 128-bit checksum as a hex string.
parse_url(url, partToExtract [, key])	Extracts a part from url.
position(substr, str [, pos])	Returns the position of the first occurrence of substr in str after position pos.
printf(format, expr [, ...])	Returns a formatted string from printf-style format strings.
regexp_count(str, regexp)	Returns the number of times the regular expression regexp matches in str.
regexp_extract(str, regexp [, idx])	Extracts a group that matches regexp.
regexp_instr(str, regexp)	Returns the position of the first match of regexp in str.
regexp_replace(str, regexp, rep [, position])	Replaces all substrings of str that match regexp with rep.
regexp_substr(str, regexp)	Returns the substring that matches the regular expression regexp within str.
repeat(str, n)	Returns the string that repeats str n times.
replace(str, search [, replace])	Replaces all occurrences of search with replace in str.
reverse(str)	Returns str with the order of the characters reversed.
right(str, len)	Returns the rightmost len characters from the string str.
rtrim([trimStr,] str)	Returns str with trailing characters removed.
sha2(expr, bitLength)	Returns the SHA-2 family of hash functions.
split_part(str, delimiter, partNum)	Splits str by delimiter and returns the requested part.
startswith(left, right)	Returns true if left starts with right.
substr(str, pos [, len])	Returns the substring of str starting at pos with length len.
substring(str, pos [, len])	Returns the substring of str starting at pos with length len.
substring_index(str, delim, count)	Returns the substring before count occurrences of delim.
to_char(expr [, fmt])	Converts expr to a string.
to_number(expr [, fmt])	Converts string expr to a number.
translate(input, from, to)	Translates input by replacing characters in from with corresponding characters in to.
trim(str [, trimStr])	Removes leading and trailing characters from str.
ucase(str)	Returns str with all characters changed to uppercase.
upper(str)	Returns str with all characters changed to uppercase.
url_encode(str)	Encodes str for use in URL query parameters.

Miscellaneous functions

The following functions are also supported:

Function	Description
AGGREGATE OVER	Calculates values across time ranges or other ordered sets for measures.
CASE expr { WHEN opt1 THEN res1 } [...] [ELSE def] END	Returns resN for the first optN that equals expr or def if none matches.
CASE { WHEN cond1 THEN res1 } [...] [ELSE def] END	Returns resN for the first condN evaluating to true, or def if none found.
coalesce(expr1, expr2 [, ...])	Returns the first non-null argument.
decode(expr, search, result [, search, result]... [, default])	Compares expr to each search value and returns the corresponding result.
get_json_object(json_txt, path)	Extracts a JSON object from json_txt at the specified path.
greatest(expr [, ...])	Returns the greatest value of all parameters.
hash(expr1 [, expr2 ...])	Returns a hash value of the arguments.
if(condition, true_value, false_value)	Returns true_value if condition is true, otherwise returns false_value.
iff(condition, true_value, false_value)	Returns true_value if condition is true, otherwise returns false_value.
ifnull(expr1, expr2)	Returns expr2 if expr1 is null, or expr1 otherwise.
json_array_length(jsonArray)	Returns the number of elements in the outermost JSON array.
least(expr [, ...])	Returns the least value of all parameters.
monotonically_increasing_id()	Returns monotonically increasing 64-bit integers.
nullif(expr1, expr2)	Returns NULL if expr1 equals expr2, otherwise returns expr1.
nvl(expr1, expr2)	Returns expr2 if expr1 is NULL, or expr1 otherwise.
nvl2(expr1, expr2, expr3)	Returns expr2 if expr1 is not NULL, or expr3 otherwise.
schema_of_json(json [, options])	Returns the schema in the DDL format of the JSON string.