Articles in this section

See more

Blue Triangle Business Intelligence: Functions and Operators

Cat Rugari
  • Updated
Follow

Table of Contents

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

Related articles

Comments

0 comments

Please sign in to leave a comment.