How to exclude a specific pivoted column from table calculation?

KukHwa
Participant I

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

Solved Solved
0 6 1,910
1 ACCEPTED SOLUTION

Dawid
Participant V

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

View solution in original post

6 REPLIES 6

David_P1
Participant V

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!

Dawid
Participant V

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

KukHwa
Participant I

Thank you very much!

It works!

KukHwa
Participant I

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!

Dawid
Participant V

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)

KukHwa
Participant I

Thank you so much!