[Retired] Computing an Average Across Pivot Columns


(sam) #1

The content of this article has been updated and migrated to a Looker Help Center article.


(Jimmy Le Thai) #2

Thanks for the writeup Sam! This helped me a lot.

I just wanted to leave another bit that I ran into with my dataset:

sum(pivot_row(${order_items.count})) / max(pivot_row(pivot_column()))

Doesn’t take into account null values, but using the mean function will. The next iteration on this elegant average is:

mean(pivot_row(${order_items.count}))

:slight_smile:


(sam) #3

Awesome @jlthai - thanks for sharing that elegant iteration!


#4

Is it possible to exclude the last column from mean(pivot_row)? I’m trying to calculate MTD average and only want to include data up to yesterday because results from today are not complete yet?


(Izzy) #5

@uyen

This is pretty easy to do using a nifty way to lookup the last column. We can use max(pivot_row(pivot_column())) to find the index number of the very last column, and then we can exclude it in our final query like:

mean(pivot_row(if(pivot_column()=max(pivot_row(pivot_column())),null,${order_items.count}))) Where we return a null in the mean function if the pivot_column is equal to the max pivot column.

Give that a whirl!