Question

Using pivot_index in table calculations (3.28+)

  • 26 August 2015
  • 1 reply
  • 3394 views

Userlevel 4
Badge
  • Looker Staff
  • 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

1 reply

 

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)

 

 

Reply