Guided and Custom Formulas

The Calculated Column Action allows you to use formulas to transform your data. You can either use one of our guided formulas, or write your own formula using the Custom formula option. When adding a Calculated Column, you’re automatically prompted to choose a formula type.

Note: If you aren’t seeing a formula type you expect to see for an existing column, check the column’s data type. Some formulas are only available for numeric columns.

Choose a Guided or Custom Formula when adding a Calculated Column

Tip! When dividing integers, be sure to multiply the top value by 1.0 to convert your results to a decimal. For example:

(1.0 * "Count of Clicks")/"Ad Cost"

Guided Formulas

When selecting a Guided Formula, you’re prompted to select values for each required field. There’s also additional text to help you decide which column to choose or what value to input for each field. No need to worry about the syntax–we handle it for you.

Single Row Operations Multi Row Operations Custom
Add Aggregation Custom Formula
Column Ratio Lag
Create Link with Title Moving Average
Date Difference Percent Change
Divide Percentile
Extract Text Ratio of Total
Format Running Total
Multiply
Round
Subtract
Total Column Sum

Single Row Operations

Add

Add a numerical value to a column. If you want to add a column to another column, you’ll need to use a Custom Formula.

Add a numerical value to each row in the chosen column

Column Ratio

Easily divide one column by another in your result set to get a ratio. Integers are auto-converted to decimals to ensure decimal accuracy.

First choose a column for the numerator, then choose a column for the denominator.

Divide one column by another to get a ratio

Use a text column and a URL column and generate a Markdown link that’s clickable from your Table chart, using the text column as the link title.

Useful for creating short, easily parsable links.

Create Markdown links for Table charts

Date Difference

Calculate the amount of time between two dates. Choose which time unit the value is calculated in: days, weeks, months, etc.

Calculate the difference in days between two date columns

Divide

Divide a column by a value. If you want to divide a column by another column, you’ll need to use a Custom Formula.

Divide values in a column by a numerical value

Extract Text

Select a specific part of a string, where that substring is added to a new column. Choose the starting index (1 being the first letter), then specify a length or leave it empty to include all characters to the end of the string.

Extract a substring of your text column

Format

Specify decimal precision (i.e., number of decimal places), useful for force-formatting currency. Format returns a string, not an integer. Therefore, it is best used in table charts or Single Value charts.

Specify decimal precision

Multiply

Multiply a column by a value. If you want to multiply a column by another column, you’ll need to use a Custom Formula.

Multiply values in a column by a numerical value

Round

Round is used to round to a specific number of decimal places.

Note: You cannot round a number to a higher decimal precision than it already has. For example, you cannot round the integer 10 to have two decimal places.

Round a number to the specified number of decimal places

Subtract

Subtract a value from a column. If you want to subtract a column from another column, you’ll need to use a Custom Formula.

Subtract values in a column by a numerical value

Total Column Sum

Create a new column where each value is a sum of the values in its row.

Calculate the sum of values in a row

Multi Row Operations

Aggregation

Create a new column with an aggregation value of another column. Choose from AVG, COUNT, MAX, MEDIAN, MIN, or SUM.

Useful for applying calculations to a column that involve an aggregated value. For example, use the SUM aggregation then add another Calculated Column and use Column Ratio to determine a column’s value percentage of the total column.

Summarize your rows in a new column

Lag

Access data from a previous row in the same column. Row offset defines how many rows back you want to use.

Access data from a previous row

Moving Average

Generate a series of averages of your data to create a smooth trend line. Trailing rows is the number of previous rows to include in each row’s average.

Generate a moving average with three trailing rows

Percent Change

Determines percent change between current and previous row.

Calculate the percent change between the total cost of each plan type

Percentile

Determines the percentile of each value in a numeric column. For example, a value is at the 50th percentile if half of the values in the column are less than or equal to it.

Determine the percentile of each day's activity count

Ratio of Total

Displays each row’s value as a ratio of the column sum (i.e., the row’s value divided by the column’s total sum).

Calculate the ratio of between each plan's cost to the total cost

Running Total

Cumulative sum of all previous results of a column (i.e., row1, row1+row2, row1+row2+row3, etc.).

Get a running total of the plan cost

Custom

Custom Formula

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.

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)
  • pow(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 Calculated 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

String functions

replicate, charindex, leftstr, rightstr, ltrim, rtrim, trim, replace, reverse, proper, padl, padr, padc, strfilter

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.