Solved

How to exclude a specific pivoted column from table calculation?

  • 26 February 2021
  • 6 replies
  • 734 views

Hello Looker community,

 

if I want to calculate a Mean of total across the whole row, EXCEPT the last column (80 or Above), what should I do? 

Mean (pivot_row(products Count)…???)

Aggregating_across_rows__row_totals__in_table_calculations__3.36__.3.png

icon

Best answer by Dawid 27 February 2021, 09:12

View original

This topic has been closed for comments

6 replies

Userlevel 3
Badge

Hi @KukHwa,

One way to compute operations on selected pivots columns is by using pivot_index.

This should give you the mean of all, but the last column.

(
pivot_index(${products.count}, 1)+
pivot_index(${products.count}, 2)+
pivot_index(${products.count}, 3)+
pivot_index(${products.count}, 4)+
pivot_index(${products.count}, 5)+
pivot_index(${products.count}, 6)+
pivot_index(${products.count}, 7) ) / 7

Best!

Userlevel 7
Badge +1

Another way is to use row’s total - pivot_where(${users.age} = “80”)

Thank you very much!

It works!

Both work perfectly!

 

But, I wonder if

there is any way to use mean function rather than manually computing total/number of values. I would need to calculate median, standard deviation for the selected pivoted columns as well, so I would prefer to use functions rather than manually computing.

 

Let me know!

 

 

Userlevel 7
Badge +1

I tested it on a similar scenario and achieved mean from the selection - 1 option in two ways:

 

mean(pivot_row(if(${users.age} = "80+", null, ${products.count})))

or

(${products.count:row_total} - pivot_where(${users.age} = "80+", ${products.count:row_total})) / (count(pivot_row(${users.age})) - 1)

 

Thank you so much!