Formula Functions
For calculations in formulas, Ultorg defines a standardized set of available functions, listed below. Except where noted, each function behaves the same on every data source.
Function documentation is also available from within Ultorg, while editing any formula.
For query execution, Ultorg translates each function to appropriate dialect-specific SQL code. This saves the user from having to look up functions in each vendor's SQL documentation.
The tables below include a description of each function along with the data type returned and the data types expected for input values to the function (parameters).
Individual function descriptions have a “more” link with additional details. These concern:
- The handling of null values.
- The handling of available precision in Number values.
Some functions, such as plus and sum, can preserve exact precision when inputs originate from decimal-based storage types such as PostgreSQL's numeric. This is useful when dealing with monetary amounts.
- The handling of “Infinity” and “NaN” values in Number inputs. Most commonly, such values will be treated as if they were null values.
- The handling of different collations (language settings) for Text values.
- The handling of time zone offsets in Global Timestamp inputs.
Operators
Ultorg formulas can contain mathematical expressions, using the standard notation that is seen in Excel, SQL, and most programming languages. For example:
![The example formula '=[Unit Price] * [Qty] * (1 - [Discount])', which contains the multiply and minus operators, as well as parentheses to adjust the order of operations.](/images/docs/ref/operators-example.png){kind=small}
Functions such as plus and minus, which are written with symbols, are known as operators. Ultorg's supported operators are listed below, in groups of decreasing precedence, e.g. multiplicative operators (*/%) before additive operators (+-).
Arithmetic on dates and times works like in Excel or Google Sheets, with durations expressed in fractions of a day. The plus and minus operators accept various combinations of data types as input, e.g. endDate - startDate to compute the number of days between two dates.
SQL expressions that are generated from Ultorg formulas will be fully parenthesized. Thus, the operator precedence of individual SQL dialects is irrelevant.
base ** exponent (power) | Raise one number to the power of another. The result is either a finite number or null. Null is returned if the result is mathematically undefined, i.e. if (1) the base is negative and the exponent is not an integer or (2) the base is zero and the exponent is zero or negative. (More...) |
factor1 * factor2 (multiply) | Multiply two numbers. (More...) X ← X * X where X is of type Number |
dividend / divisor (divide) | Divide one number by another. Division by zero yields a null result. (More...) |
dividend %% divisor (modulo) | Calculate the remainder (modulo) when dividing two numbers. Division by zero yields a null result. The result has the same sign as the divisor (like in Excel, Python, and R). (More...) |
term1 + term2 (plus) | Add two numbers. (More...) X ← X + X where X is of type Number |
date + time (timestamp) | Create a single local timestamp value from a date and a time. Shorthand for timestamp. |
date + days (addDays) | Add whole calendar days to a date. Shorthand for addDays. |
timestamp + days (addDayFract) | Add elapsed time to a timestamp, in fractions of a day. Shorthand for addDayFract. |
minuend - subtrahend (minus) | Subtract one number from another. (More...) X ← X - X where X is of type Number |
endDate - startDate (diffDays) | Get the number of whole calendar days between two dates. Shorthand for diffDays. |
endTime - startTime (diffDayFract) | Get the elapsed time between two time values, in fractions of a day. Shorthand for diffDayFract. |
value1 is value2 | Test if any two values are equal or both null. This function works exactly like equals ("="), except handles several additional cases where the latter would return null or not be permitted. The comparison defined by this function is the same as is used to collapse groups of repeating values for aggregation and presentation purposes. (More...) Logical ← X is X where X is any type |
value1 is not value2 | Test if any two values are not equal, nor both null. The logical opposite of is. (More...) Logical ← X is not X where X is any type |
value1 >= value2 (greaterOrEquals) | Test if the first value is greater than or equal to the second. (More...) |
value1 > value2 (greater) | Test if the first value is strictly greater than the second. (More...) |
value1 <= value2 (lessOrEquals) | Test if the first value is less than or equal to the second. (More...) |
value1 < value2 (less) | Test if the first value is strictly less than the second. (More...) |
value1 = value2 (equals) | Test if two values are equal. All matches are exact. The comparison defined by this function is the same as is used to match values in a join between two tables. (More...) |
value1 <> value2 (notEquals) | Test if two values are not equal. The logical opposite of equals ("="). (More...) |
not condition | Return true if the input value is false. (More...) |
condition1 and condition2 | Return true if both of the input values are true. If either input is false, the result is always false, even if the other input is null. Otherwise, the result is null if either input is null. (More...) |
condition1 or condition2 | Return true if at least one of the input values is true. If either input is true, the result is always true, even if the other input is null. Otherwise, the result is null if either input is null. (More...) |
Aggregate Functions
Aggregate functions are functions which process values from multiple rows, yielding a single value per group. See the linked documentation page for more information.
For example, the following formula uses the max aggregate function to find, for each course, the maximum capacity of any section:
Supported aggregate function are listed below.
count(valueOrGroup) | Count values or rows within each group. The input may reference a specific field within a subquery, or the subquery itself. In the former case, null values are not counted. For empty groups, return zero. Repeated values are included in the count. To count distinct values, use the countd function, or use Custom Group to gather distinct values first. (More...) |
countd(value) | Count distinct values within each group. Null values are ignored. For empty groups, return zero. The distinctness of values is defined as per the is function. (More...) |
countif(condition) | Count rows for which a condition is true, within each group. For example, countif(age < 12) counts the number of rows for which the age field is less than 12. This function is equivalent to sum(if(condition, 1, 0)), and is provided as a shortcut. Note that you can also use the regular Filter action to restrict inputs to an aggregate function. (More...) |
sum(number) | Add numbers within each group. Null values are ignored. For empty groups, return zero (not null). (More...) |
min(value) | Find the smallest value in each group. Null values are ignored. For empty groups, return null. (More...) |
max(value) | Find the largest value in each group. Null values are ignored. For empty groups, return null. (More...) |
single(value) | For each group with exactly one distinct value, return that value. Null values are ignored. For groups without a single distinct value, return null. The distinctness of values is defined as per the is function. (More...) X ← single(X) where X is any type |
avg(number) | Calculate the average of numbers within each group. This is the sum of numbers divided by the number of values (arithmetic mean). Null values are ignored. For empty groups, return null. (More...) |
stdev(number) | Calculate the sample standard deviation within each group. Null values are ignored. For groups with fewer than two values, return null. (More...) |
stdevp(number) | Calculate the population standard deviation within each group. Null values are ignored. For empty groups, return null. (More...) |
rawSQLagg(sqlExpression, arguments, ...) | Get a value from a raw SQL aggregate expression. This function works like rawSQL, but must be used when the provided SQL expression contains an aggregate function. This will ensure the proper automatic inclusion of a GROUP BY clause when necessary. If the result is a number, use rawSQLaggNum instead. (More...) |
rawSQLaggNum(sqlExpression, arguments, ...) | Get a number from a raw SQL aggregate expression. This function works like rawSQLagg, but treats the result as a Number value. (More...) |
Scalar Functions
Scalar functions produce one value per input row. They can be used in combination with aggregate functions, or by themselves.
Here is an example formula that uses the scalar functions concat, year, and if:
Supported scalar function, except operators, are listed below by category.
Logical
if(condition, ifTrue, ifFalse) | Pick one value or the other depending on a logical condition. Return null only if the condition is null, or if the selected value is null. (More...) |
ifnull(value, defaultIfNull) | Replace null with a default value. If value is null, return the defaultIfNull, otherwise return value. Similar behavior can be achieved with the if function; this function is provided as a shortcut. See also zn. (More...) X ← ifnull(X, X) where X is any type |
zn(number) | Replace null with zero. If the input number is null, return zero, otherwise return the input number. Similar behavior can be achieved with the if function; this function is provided as a shortcut. This function may be useful e.g. when working with data from a spreadsheet, where blank cells are intended to be interpreted as zero. (More...) |
Mathematical
abs(number) | Get the absolute value of a number. In other words, return the input without its sign, yielding a non-negative number. (More...) |
round(number, numDigits) | Round a number to the specified number of fractional digits. A negative number of digits may be specified to round to tens, hundreds, thousands etc. The digit 5 may be rounded up or down depending on the data source. Up to 14 fractional digits may be specified. (More...) |
ceil(number) | Round a number up to the next whole number. For example, ceil(2.3) returns 3. (More...) |
floor(number) | Round a number down to the previous whole number. For example, floor(5.8) returns 5. (More...) |
log(number) | Calculate the decimal logarithm of a number. Return null if the input is zero or negative. (More...) |
ln(number) | Calculate the natural logarithm of a number. Return null if the input is zero or negative. (More...) |
Text
len(text) | Get the number of characters in a text string. Includes any leading or trailing whitespace characters. For Binary values, return the number of bytes. (More...) |
contains(haystack, needle) | Test if the first text string contains the second. Searching for an empty string always yields a match. (More...) |
icontains(haystack, needle) | Test if the first text string contains the second, ignoring case. Searching for an empty string always yields a match. On some data sources, this function may also be accent-insensitive and kanatype-insensitive. This function is the same as is used in "contains" filters. (More...) |
startsWith(haystack, needle) | Test if the first text string starts with the second. Searching for an empty string always yields a match. (More...) |
endsWith(haystack, needle) | Test if the first text string ends with the second. Searching for an empty string always yields a match. (More...) |
find(haystack, needle) | Find the character position of the second text string in the first. Return the character position of the first match, starting from zero, or null if no match is found. Searching for an empty string always produces a match at position zero. (More...) |
replace(haystack, needle, replacement) | Replace all occurences of one text string inside another. If the string to search for (needle) is empty, the original string (haystack) is returned. (More...) |
left(text, numChars) | Get a portion of a text string, starting from the beginning. If the specified number of characters is zero or negative, an empty string is returned. (More...) |
right(text, numChars) | Get a portion of a text string, starting from the end. If the specified number of characters is zero or negative, an empty string is returned. (More...) |
mid(text, startPos, length) | Get the specified portion of a text string. Starting position zero denotes the beginning of the string. If the specified length is zero, an empty string is returned. A negative position or length is treated as zero. (More...) |
upper(text) | Transform text to all uppercase. For example, "Hello" becomes "HELLO". (More...) |
lower(text) | Transform text to all lowercase. For example, "Hello" becomes "hello". (More...) |
trim(text) | Remove spaces at the beginning and end of a text string. Other kinds of whitespace characters are not removed. (More...) |
concat(value, ...) | Convert a value, or a series of values, to a text string. Null values are ignored. Two non-null values of the same type will have the same text representation if and only if the original values compare as identical per the is function. For date and time types, the exact string representation depends on the data source, and may change in the future. (More...) |
urlEncode(text) | Encode text for use in a URL query parameter. For example, John Smith becomes John%20Smith, which could be used in the URL https://www.example.com/search?q=John%20Smith. This function is analogous to encodeURIComponent in JavaScript, though with percent-encoding applied to a more minimal set of reserved characters. (More...) |
num(text) | Convert a text string to a number. If the string is not a number, return null. Valid numbers are whole numbers (e.g. "23", "0") and fractions with a period as a decimal point ("4.2", "4.", ".25"), plus either of the latter with a leading sign ("-4.2", "+23") and/or a trailing exponent ("4.2e3", 4.2e-12", "4.2E+3"). Leading and trailing spaces are ignored, as are leading zeroes, including in the exponent ("0043", "4.2e02"). NaN and Infinity values are not recognized. See also int. (More...) |
int(text) | Convert a text string to a whole number. If the string is not a whole number, return null. Valid whole numbers are strings of digits (e.g. "23", "0"), possibly with a leading sign (e.g. "-4", "+23"). Leading and trailing spaces are ignored, as are leading zeroes. Values are assumed to be in the range of a 64-bit signed integer (BIGINT type in SQL). See also num. (More...) |
uuid(text) | Create a UUID value from a text string. A UUID is a 128-bit identifier of the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, where each x is a hexadecimal digit (0-9/A-F, case-insensitive). If the string is not a valid UUID, return null. (More...) |
binary(hexString) | Create a binary value from a hexadecimal text string. Each byte must be specified in the form xx, where x is a hexadecimal digit (0-9/A-F, case-insensitive). For example, "596F2E" becomes a 3-byte binary value. If the input is not a valid hexadecimal string, return null. (More...) |
JSON
jsonParse(textToParseAsJSON) | Parse JSON data from a text string. If the input is not valid JSON, the entire query may fail with an error, or null may be returned, depending on the data source. To extract a primitive value from the parsed JSON data, you can use jsonValue(jsonParse(...), ...). (More...) |
jsonValue(json, fieldNameOrArrayIndex, ...) | Extract a primitive value, as plain text, from JSON data. You can provide a sequence of Text or Whole Number values to traverse JSON object keys and zero-based JSON array indices, respectively. For example, jsonValue(json, "someKey", 2), when used with the JSON data {"someKey": [500, 501, 502]}, will return the text value "502". JSON string values are returned unquoted. If an object key or array index is not found, or if the selected value is a JSON array, a JSON object, or a JSON null value, return null. To convert the text result to a Number, use the num function. (More...) |
Date & Time
makeDate(year, month, day) | Create a date from individual year, month, and day fields. If the month number is outside the range 1-12, counting continues into the adjacent year. For instance, makeDate(2018, 15, 18) returns the date 2019-03-18. Overshooting day numbers are handled similarly, counting into the adjacent month or months. This function is equivalent to Excel's "date" function. (More...) |
addDays(date, days) Shorthand: date + days date - days | Add whole calendar days to a date. To subtract days, a negative number may be used. (More...) |
addMonths(date, months) | Add whole calendar months to a date. The day-of-month will be left unchanged when possible. If the original day-of-month would lie beyond the end of the target month, the last day of the latter is used instead. To subtract months, a negative number may be used. (More...) |
addYears(date, years) | Add whole calendar years to a date. To subtract years, a negative number may be used. This function is equivalent to addMonths(date, years * 12). (More...) |
addDayFract(timestamp, days) Shorthand: timestamp + days timestamp - days | Add elapsed time to a timestamp, in fractions of a day. To subtract time, a negative number may be used. If the input is a global timestamp, it is first converted to UTC, where no DST rules apply. Thus, each day is always 24 hours long. See also diffDayFract. (More...) |
diffDays(endDate, startDate) Shorthand: endDate - startDate | Get the number of whole calendar days between two dates. If the end date is before the start date, the result is negative. (More...) |
diffMonths(endDate, startDate) | Get the number of whole calendar months between two dates. If the end date is before the start date, the result may be negative. See also diffYears. (More...) |
diffYears(endDate, startDate) | Get the number of whole calendar years between two dates. This function can be used e.g. to find a person's age given a current date and a birth date. If the end date is before the start date, the result may be negative. This function is equivalent to diffMonths(endDate, startDate) / 12, rounded towards zero. (More...) |
diffDayFract(endTime, startTime) Shorthand: endTime - startTime | Get the elapsed time between two time values, in fractions of a day. If the end time is before the start time, the result is negative. For global timestamps, the calculation is performed at UTC, where no DST rules apply. Thus, each day is always 24 hours long. See also addDayFract. The returned number may be formatted as a duration. (More...) |
timestamp(date, time) Shorthand: date + time | Create a single local timestamp value from a date and a time. (More...) |
globalUTC(timestamp) | Mark a timestamp with a time zone offset of zero (UTC). (More...) |
localUTC(timestamp) | Convert a global timestamp to a local one, at UTC. (More...) |
local(timestamp) | Convert a global timestamp to a local one, at the stored time zone. For data sources that store all global timestamps in UTC, this function is equivalent to localUTC. (More...) |
week(date) | Get the week number using January 1/Sunday-based rules (1-54). Under these rules, the first week of the year starts on January 1, and remaining weeks start on Sundays thereafter. Thus, the first and last week of the year may contain fewer than 7 days. This function is equivalent to Excel's "weeknum(date)" function. (More...) |
weekday(date) | Get the weekday as the number of days since Sunday (0-6). (More...) |
weekdayName(date) | Get the name of the weekday (e.g. "Tuesday"). The result is a Text value, but will be ordered chronologically if sorted on. To abbreviate the weekday name, use the left function. (More...) |
dayOfYear(date) | Get the day of the year (1-365/366). (More...) |
year(date) | Get the year field (e.g. 1984). All digits are included. Ancient years may be zero (denoting 1 BCE) or negative. If the input is a global timestamp, it is first converted to UTC. (More...) |
month(date) | Get the month field (1-12). If the input is a global timestamp, it is first converted to UTC. (More...) |
monthName(date) | Get the name of the month (e.g. "April"). The result is a Text value, but will be ordered chronologically if sorted on. To abbreviate the month name, use the left function. If the input is a global timestamp, it is first converted to UTC. (More...) |
day(date) | Get the day field (1-31). (More...) |
hour(time) | Get the hour field (0-23). (More...) |
minute(time) | Get the minute field (0-59). (More...) |
second(time) | Get the second field, including any fraction (0.0-59.999...). (More...) |
offsetHrs(time) | Get the time zone offset, in hours (usually zero; rarely useful). This function is relevant primarily when an existing data source contains values stored with individual time zone offsets other than UTC. For timestamps, the preferred way to apply stored time zone offsets is via the localUTC function. (More...) |
region(timestamp) | Get the time zone region identifier (usually empty; rarely useful). Most data sources do not allow full region identifiers to be stored in timestamps. The preferred way to apply stored time zone offsets is via the localUTC function. (More...) |
date(timestamp) | Get the date portion of a local timestamp. (More...) |
time(timestamp) | Get the time portion of a local timestamp. (More...) |
fromEpoch(secondsSince1970) | Get a timestamp by converting from UNIX Epoch time. This format represents time as the number of seconds elapsed since the start of 1970, at UTC. This function is equivalent to addDayFract({1970-01-01 00:00Z}, secondsSince1970 / (24 * 60 * 60)), and is provided as a shortcut. (More...) |
today() | Get the current date. The time and time zone is taken from the local computer. See also now. (More...) |
now() | Get the current time as a global timestamp, at UTC. The time is taken from the local computer, at seconds-precision. See also today. (More...) |
sqlInterval(months, days, seconds) | Construct a SQL INTERVAL value (rarely useful). While the SQL Interval type is supported on some SQL-based data sources, its use is discouraged here in favor of the dedicated addYears, addMonths, and addDayFract functions. This function is provided for completeness. (More...) |
sqlIntervalDayFract(sqlInterval) | Convert a SQL INTERVAL value to a number of fractional days. The SQL Interval type may occur in some SQL-based data sources. For the purpose of the conversion, this function assumes 24 hours per day and 30 days per month. (More...) |
Raw SQL
rawSQL(sqlExpression, arguments, ...) | Get a value from a raw SQL expression. The rawSQL functions can be used to work with data types and database functions other than those in Ultorg's standardized list. The provided SQL expression is passed unmodified to the data source, except replacing bracketed numbers with input arguments. For example, rawSQL("some_function([1], [2])", 91, 92) will be passed to the data source as some_function(91, 92). Literal bracket characters can be included by doubling them (e.g. ]]). Each expression must produce a single result value per input row. For expressions containing an aggregate function, use rawSQLagg instead. To get a more specific result type, use rawSQLbool, rawSQLnum, or rawSQLtext instead. |
rawSQLbool(sqlExpression, arguments, ...) | Get a logical condition from a raw SQL expression. This function works like rawSQL, but treats the result as a Logical value. |
rawSQLnum(sqlExpression, arguments, ...) | Get a number from a raw SQL expression. This function works like rawSQL, but treats the result as a Number value. |
rawSQLtext(sqlExpression, arguments, ...) | Get text from a raw SQL expression. This function works like rawSQL, but converts the result to a Text data type using a conversion appropriate for the SQL dialect of the data source. |