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.
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
coalesce table calculation functions combined with an appropriate
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.
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).
Finally, I wanted the values to round and look USD certified so I added the round function and a respective Value Format.
Value Format: $#00.00