One of the most useful features of Looker Table Calculations is the ability to reference values in other rows or columns of a query, without having to write crazy window functions or derived tables in SQL.

Here are some examples:

# Using the `offset()`

function

Let's say I have order profit by week:

I want to know how each week compares to the previous week, so I add a table calculation with the formula:

This gives me a third column (green, so I know it's not part of the SQL result set):

You can also do calculations like moving averages:

The table calculation editor is there to help you with autocomplete and function definitions:

# Using the `pivot_offset()`

function

Now, you want to look at the data by user state, so you add Users State and pivot by Orders Created Week:

With data like this, we can still get the week-over-week change by using the `pivot_offset()`

function:

Voilà:

When you have pivots, table calculations can be useful in other ways as well. For example, you can create columns that reference the pivot value by creating a table calculation that references the pivoting field's name:

which yields: