Guided formulas

The “Formula column” and “Apply formula” steps allow 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 “Formula column” or “Apply formula” step, it automatically prompts you to choose a formula type.

When selecting a guided formula, it then prompts you 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.

Singe-row operations

Guided formulas that perform single-row operations only use the values in one row to produce its result.

Add

Adds a static numeric value to a column

Column ratio

Divides one column by another in your result set to get a ratio

It automatically converts integers to decimals to ensure decimal accuracy.

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

Create link with title

Creates a clickable Markdown link in your table chart

First select the text column for the link label, then select the URL column for the link destination. This formula is useful for creating short, easily parsable links.

Date difference

Calculates the amount of time between two dates.

First select the column with your starting dates, then select the column with your ending dates, then select which time unit to calculate the date difference in (second, minute, hour, day, week, month, or year).

Divide

Divides a column by a static value

Extract text

Extracts a specific part of a string

First, select the column to get the substring from. Next, provide the starting position of the substring (1 is the first letter). Lastly, specify a length or leave it empty to include all characters to the end of the string.

Format

Specifies decimal precision (in other words, the number of decimal places)

This is useful for force-formatting currency.

This guided formula returns a string, not an integer; therefore, it’s best used in table charts or single value charts.

Multiply

Multiplies a column by a static value

Round

Rounds to a specific number of decimal places

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.

Subtract

Subtracts a static numeric value from a column

Total column sum

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

Multi-row operations

Guided formulas that perform multi-row operations use the values in other rows to produce its result.

Aggregation

Creates a new column by aggregating the values of another column. Available aggregation options include:

• Average

• Count

• Max

• Median

• Min

• Sum

This is useful for applying calculations to a column that involve an aggregated value. For example, use the “Sum” aggregation then add another “Formula column” step and use the “Column ratio” guided formula to determine a column’s value percentage of the total column.

Lag

Accesses data from a previous row in an existing column

Row offset defines the number of rows back from the current row to use.

This is useful for comparing the value of a row with the value of the previous row.

Moving average

Generates 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.

Percent change

Determines the percent change between the current and previous row

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.

Ratio of total

Displays each row’s value as a ratio of the column sum (in other words, the row’s value divided by the column’s total sum)

Running total

Takes a cumulative sum of all previous results of a column

The general pattern is like this: row1, row1+row2, row1+row2+row3, etc.