Custom formulas

Custom formulas use SQLite syntax and allow you to perform basic calculations or apply one or more functions to your data. You can either manually write your own custom formulas or use AI to write your SQLite expressions to help you speed up your chart editing process.

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.

Custom formulas only affect the values of one column at a time. However, you can reference multiple columns in a single custom formula to form the SQLite expression (for example, "Column A" + "Column B").

If you want to use a column from your result set in your 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. Read 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)

Read 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