Question

# Using pivot_index in table calculations (3.28+)

• 6504 views

Userlevel 4 • Member
• 75 replies

### 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
``````

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)``