Using pivot_index in table calculations (3.28+)

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:

2c00724363e96f389d68591387490d7c5c72936f.png

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
5 2 10.7K
2 REPLIES 2

Kehinde
Participant II

Using Offset, this should work.

What do you think @Zam 

round(

coalesce(pivot_offset(${orders.profit_per_user},0),0)+

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

,2)

 
Top Labels in this Space
Top Solution Authors