Using pivot_index in table calculations (3.28+)


(Zachary Michel) #1

###Introduction
As of Looker 3.28, we may use the pivot_index function to compute a single calculation across the select pivoted columns. This function may also be used to extract a single column of values to display on the far right of the table.

###What is pivot_index?
The function itself provides us the ability to reference a specific column in the table using an indexed value. The index moves from left to right with the far most left column being an index = 1.

###How is this different than pivot_offset?
The function, pivot_offset, is a relation to the current column with offset x, while pivot_index is a relation to a specific column of the table as whole given an index = y . Essentially, offset works per column, while index works across an entire table.

###An Example
Let’s say I have a table of Orders, and within the Orders view I’ve computed whether or not each order was a first purchase, using an Is First Purchase (Yes / No) dimension. Subsequently, I’ve also computed the Profit per User in my Orders view, which I will intend to use grouped by Orders Created Date.

To form this report I will use Orders Created Date as my dimension, Orders Is First Purchase (Yes / No) as my pivot, and Orders Profit per User as my measure. Here is the table in its first pass state:

Now that we have our table, I would like to compute the Total Profit per User for each day. To do so, I’ll use a combination of the round, pivot_index, and coalesce table calculation functions combined with an appropriate Value Format.

Here is the result - notice how my calculation is displayed on the right edge of the table:

The calculation including the with relevant value format is as follows:

###How does this calcluation work?
First, I wanted to add the values from both columns together. To do so, I utilized pivot_index in the following calculation to add the values from the first column to the second column.

pivot_index(${orders.profit_per_user},1)+
pivot_index(${orders.profit_per_user},2)

This would suffice if there were no null values, but since there are null values, and adding nulls always results in a null, I need to coalesce both columns (for consistency).

coalesce(pivot_index(${orders.profit_per_user},1),0)+
coalesce(pivot_index(${orders.profit_per_user},2),0)

Finally, I wanted the values to round and look USD certified so I added the round function and a respective Value Format.

round(
  coalesce(pivot_index(${orders.profit_per_user},1),0)+
  coalesce(pivot_index(${orders.profit_per_user},2),0)
,2)

Value Format: $#00.00

Creating a percent of total across rows with table calculations
[RETIRED] How to refer to totals and row totals in table calculations (3.24+)
Creating a percent of total across rows with table calculations
Table Calculations (3.18 Labs Feature)
Looker 3.28 Release Notes
Filtering in Visualizations using Table Calculations (3.28+)