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:

Voilà:

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

yields:


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
Table Calculations (3.18 Labs Feature)
Week-to-Date (WTD) Comparison
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.

Best