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: