Blue Triangle Business Intelligence: Functions and Operators

Overview

BTBI Creator expressions are used to perform calculations for:

A major part of these expressions is the functions and operators that you can use in them. 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

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.

Function for Any BTBI Creator Expression

Function	Syntax	Purpose
abs	abs(value)	Returns the absolute value of `value`.
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`.
rand	rand()	Returns a random number between 0 and 1.
round	round(value, num_decimals)	Returns `value` rounded to `num_decimals` decimal places.
sqrt	sqrt(value)	Returns the square root of `value`.

Operators for any BTBI 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. String functions can also be used to format the data returned in the table.

Functions for Any BTBI Creator Expression

Function	Syntax	Purpose
concat	concat(value_1, value_2, ...)	Returns `value_1`, `value_2`, `...`, `value_n` joined as one string.
contains	contains(string, search_string)	Returns `Yes` if `string` contains `search_string`, and `No` otherwise. The `contains` function is case-sensitive.
length	length(string)	Returns the number of characters in `string`.
lower	lower(string)	Returns `string` with all characters converted to lowercase.
position	position(string, search_string)	Returns the start index of `search_string` in `string` if it exists, and `0` otherwise.
replace	replace(string, old_string, new_string)	Returns `string` with all occurrences of `old_string` replaced with `new_string`.
substring	substring(string, start_position, length)	Returns the substring of `string`, beginning at `start_position`, consisting of `length` characters. The `start_position` starts at `1`, with `1` indicating the first character in the string, `2` indicating the second character in the string, and so on.
upper	uppser(string)	Returns string with all characters converted to uppercase.

Date Functions

Date functions enable you to work with dates and times.

Functions for Any BTBI Creator Expression

Function	Syntax	Purpose
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 "`year-month-day`" date or `null` if the date would be invalid.
date_time	`date_time(year, month, day, hours, minutes, seconds)`	Returns `year-month-day 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`.
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`.
diff_months	`diff_months(start_date, end_date)`	Returns the number of months between `start_date` and `end_date`.
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`.
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.
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.

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 BTBI Creator Expression

Function	Syntax	Purpose
case	case(when(yesno_arg, value_if_yes), when(yesno_arg, value_if_yes),..., else_value)	Allows conditional logical with multiple conditions and outcomes. Returns value_if_yes for the first when case who yesno_arg value is yes. Returns else_value if all when cases are no.
coalesce	coalesce(value_1, value_2, ...)	Returns the first non-null value in value_1, value_2, ..., value_n if found and null otherwise.
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.
is_null	is_null(value)	Returns Yes if value is null and No otherwise.

Operators for Any BTBI Creator 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 can be used with numbers, dates, and strings:

Operator	Syntax	Purpose
>	value_1 > value_2	Returns `Yes` if `value_1` is greater than `value_2`, and `No` otherwise.
<	value_1 < value_2	Returns `Yes` if `value_1` is less than `value_2`, and `No` otherwise.
>=	value_1 >= value_2	Returns `Yes` if `value_1` is greater than or equal to `value_2`, and `No` otherwise.
<=	value_1 <= value_2	Returns `Yes` if `value_1` is less than or equal to `value_2`, and `No` otherwise.

You can also combine BTBI Creator Expressions with these logical operators:

Operator	Syntax	Purpose
AND	value_1 AND value_2	Returns `Yes` if both `value_1` and `value_2` are yes, and `No` otherwise.
OR	value_1 OR value_2	Returns `Yes` if either `value_1` or `value_2` are yes, and `No` otherwise.
NOT	NOT value	Returns `Yes` if value is No, and `No` otherwise.

Note: 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	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")

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