Documentation Visual SQL Guided & Custom Formulas

Custom Formula

If you joined before January 6, 2020, you might be using our old Data Explorer interface. Check out our Pre-built and Custom Formulas page for more relevant content.

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.

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

Want to use a static string in a Custom formula? Wrap the string in single quotes (i.e., '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:

(1.0 * "Mobile Events")/"Total Events"

CASE statements in Custom formulas

Check out our FAQ for examples of how to use CASE statements in Visual SQL.

Auto-complete functions

There are some auto-complete functions that appear when creating a Custom formula. The format for these functions is conveniently shown but the values must be manually typed into the field–except for column names, which have an auto-complete feature.

The following functions are available as auto-complete functions:

dateadd(values, amount, unit)
datediff(values, values, unit)
datepart(values, unit)
datesub(values, amount, unit)
format(values, format_string)
moving_avg(values, sample_size)
percent_change(num_values)
power(base, exponent)
rowid
running_total(values)

Note: Some of the above functions are now Guided Formulas! We recommend using Guided Formulas, if available.

Common functions

While there are many SQLite functions that aren’t pre-built for Formula Columns, we do support them. You can manually input other SQLite functions into the Custom formula field, 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

Check out our SQLite string functions FAQ for some examples of using the length, upper, lower, replace, instr, and substr functions.

Aggregate functions

stdev, variance, mode, median, lower_quartile, upper_quartile

Date functions

In the formulas below, units can be one of the following: day, month, year, hour, minute, second, quarter (datepart() only), dayofyear (datepart() only). For more details on these date functions, check out the SQLite date and time functions page.