Starting in Looker 21.12, you can use Quick Calculations to perform common calculations on numeric fields that are in an Explore's data table without using Looker functions and operators when the Custom Fields Labs feature is enabled, and you have permission to use them.

Table calculations, as discussed in Using Table Calculations, make it easy to create on-the-fly metrics and enable the creation of calculations that operate on the table data returned by a SQL query. This is great for calculating metrics like percent of previous and percent change.

This article shows you how to calculate these metrics, down columns and across rows.

Calculating Down Columns Using offset()

The offset() table calculation function lets you reference other rows in the same column you are operating in. This is perfect for calculating percent of previous and percent change down columns.

For an example, see the table below:

Percent of Previous

To calculate the percent of previous of orders.count, we can make a table calculation like this:

${orders.count} / offset(${orders.count}, -1)

This divides each value in the table by the value in the row above it.

Percent Change

We calculate percent change similarly to percent of previous, except we want to subtract the entire calculation by 1:

${orders.count} / offset(${orders.count}, -1) -1 

These two metrics yield the following results:

Customize the format of table calculation values by selecting a specified format from the default formatting drop-down menu in the table calculation modal. Alternatively, use custom formatting to get the formatting you want, as explained in Adding Custom Formatting to Numeric Fields (if you want something other than the available default formats).

Calculating Across Rows Using pivot_offset()

With pivoted results, the pivot_offset() table calculation function lets us reference values that are on the same row in different columns. In the following example using pivot_offset(), -1 references the previous column (rather than the previous row, as in the example above).

For an example, see the table below:

Percent of Previous

To calculate percent of previous across rows, we use pivot_offset() in the same way we used offset() above:

${orders.count} / pivot_offset(${orders.count}, -1)

This divides each value in the table by the value on the same row, located one column to the left. We get the following table:

Percent Change

Again, we can use pivot_offset() the same way we use offset() when calculating down columns:

(${orders.count} / pivot_offset(${orders.count}, -1)) - 1

This is the same as percent of previous, just subtracted by 1. This gives us these results: