Table Calculation to Grab Value(s) Associated with Last Pivoted Column

[LookerExperts]

Knowledge Drop

Last tested: May 27, 2020
 

TL;DR: index(pivot_row(pivot_column()), max(pivot_row(pivot_column()))) returns the index number of the last pivot column

Sometimes a user might have pivoted on a timeframe or date field and as such, the final pivoted column will be the most recent date with whatever associated data they want to show. This was necessary because their column chart visualization required them to pivot on their date field instead of having it as a primary dimension. And in this case, they only wanted to display the most recent data associated with the final pivoted column in the pivot 'series'.

At first...I thought max(pivot_column()) would work..but no....it evaluates within each pivoted column, not across the whole pivoted 'series'...

So, first, we can grab the pivot col # of the last pivoted column with this:

index(pivot_row(pivot_column()), max(pivot_row(pivot_column())))

This ^ is really just creating a list of pivot #'s (i.e. if you have 5 pivot columns then -> 1,2,3,4,5), then grabbing the value associated with the max of that list.

And then we can just use that evaluation as a condition to grab our values from the last column. For example, If I wanted to grab the value of the 'Count' field in the last pivoted column, we'd use:

pivot_index(${orders.count},index(pivot_row(pivot_column()), max(pivot_row(pivot_column()))))

So this is just using pivot_index with the max pivot column # condition and getting whatever value of the field we want. Yay!

This content is subject to limited support.