Aggregating across rows (row totals) in table calculations (3.36+)


#1

As of Looker 3.36, we have introduced a pivot_row function, which allows you to group together all values in a pivoted row as a list, and then aggregate over that list. Read about how lists work here.

How does it work?

Let’s see how pivot_row actually works. For example, let’s say I have this table:

If I have a table calc like this:

pivot_row(${orders.count})

This will show me a list of all the values in each pivot row, like this:

Calculating the Row Total using pivot_row

I can then perform calculations on this list. For example, I can calculate the Row Total of this data using the pivot_row function like this:

sum(pivot_row(${orders.count}))

This first grabs the pivot row of ${orders.count} as a list, and then sums that list together. This results in:

The world is your oyster

pivot_row can be used to aggregate across any pivoted row.

For example, read about how to use pivot_row to calculate a percent of total across rows here.


Using lists in table calculations (3.36+)
Creating a percent of total across rows with table calculations
Looker 3.36 Release Notes
(Rick Saporta) #2

amazing!! this was a much needed function. Thanks lookers!


#3

It would be great to have the ability to perform the Looker equivalent of SQL Window Functions over data without needing to pivot. In situations where there are a high number of dimensional relationships, pivoting the data will render the results set very wide and unmanageable.