Using offset and pivot_offset in table calculations (3.18+)


(Josh Siegel) #1

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:

1) 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:

Straightforward, right?

Now I have a third column (green, so I know it’s not part of the SQL result set):

You can also do stuff like moving averages:

And the editor is there to help you with autocomplete and function definitions:

2) Using the pivot_offset() function

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

We can get the week over week change with the pivot_offset() function:


Note: You can also reference the pivot value by referencing the field name:


Using pivot_index in table calculations (3.28+)
Calculating percent of previous and percent change with table calculations
YoY growth for a date range specified in filter
Week-to-Date (WTD) Comparison
RETIRED: Table Calculations (3.18 Labs Feature)
Rolling average using offset_list in table calculations (3.36+)
(Prasanna) #2

Hi Josh,

Thanks for sharing the article, could you please let me know the ${orders.total_order_profit} - offset($orders.total_order_profit},-1 ) works in oracle .

waiting for your favorable reply

(Aleksandrs Vedernikovs) #3

Hey @spmsarada,

Looker Table Calculations functions will work irrespective of your underline SQL dialect as they operate on the results from your query. I would advise you to read over our documentation here.


(Eran Ben Moha) #4

Hi, thanks for the article it’s very useful
Regarding pivot_offset - I have a problem
my results are pivoted by week, I show delta from previous week
It works well, but in my visualisation I don’t want to show previous week - just the delta

any idea how to hide it from visualisation?

(Izzy) #5

I don’t believe you can hide single pivot columns from the visualization, if that’s what you’re asking— But what you can do is use a pivot_where to create an entirely new table calculation that just references the delta pivot column, and then hide everything else from the visualization. Does that make sense? I can provide an example if not!