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)…???)
Solved! Go to Solution.
Another way is to use row’s total - pivot_where(${users.age} = “80”)
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!
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!
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!