Custom formulas

Perform basic calculations on your column, or combine one or more of functions as needed. SQLite functions are also supported. See the SQLite documentation for the full list of core functions, aggregate functions, window functions, and date and time functions available.

To use a custom formula:

1. Add a Formula column step or Apply formula step.

2. Select Custom as the formula type.

3. Use a built-in or custom SQLite formula.

4. Select Save.

You can use a column from your result set in a custom formula. Just make sure to wrap the column name in double quotes ("column name"). As you type the column name, it will appear as an auto-complete option if it’s a valid column to use.

You can also use a static string in a custom formula. Wrap the string in single quotes ('static string').

Integer division

When dividing integers, be sure to multiply the top value by 1.0 to convert your results to a decimal; otherwise, if you’re trying to calculate ratios, you may end up with a bunch of 0s, which probably isn’t what you want. For example:

CASE statements in custom formulas

Use SQLite CASE statements in custom formulas to alter results or perform calculations using conditional logic. Learn more about SQLite CASE statements.

Units for date and time functions

For the formulas mentioned in the next sections, you can use any of the following units in functions involving dates:

• day

• month

• year

• hour

• minute

• second

• quarter (datepart() only)

• dayofyear (datepart() only)

Learn more about SQLite date and time functions.

Built-in functions

There are a variety of built-in functions that appear when creating a custom formula. It shows the parameters for each function, but you’ll need to manually type the values into the field—except for column names, which have an auto-complete feature.

Remember to wrap column names in double quotes (“) and static strings in single quotes (') when you use them in one of these built-in functions.

Common functions

While there are many SQLite functions that aren’t built-in for formula columns, we do support them. You can manually input other SQLite functions into a custom formula, and they’ll work as expected.

Here are some common functions you might use:

Math functions

acos, asin, atan, atn2, atan2, acosh, asinh, atanh, difference, degrees, radians, cos, sin, tan, cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign, sqrt, square, ceil, floor, pi, random

String functions

length, upper, lower, replicate, instr, substr, charindex, leftstr, rightstr, ltrim, rtrim, trim, replace, reverse, proper, padl, padr, padc, strfilter

View some examples of using SQLite string functions.

Aggregate functions

stdev, variance, mode, median, lower_quartile, upper_quartile