Clever Calculations

Table Calculations are one of Looker’s really handy features, allowing users to augment the results of their queries with calculations using a spreadsheet-style expression syntax. Quick Calculations can automatically add common calculation types without needing any expression code. Here’s an example to show how both of these work.

It’s common to have data on product ratings by your customers. Here’s a simple results table showing the number of orders per day, pivoted by the ratings, 1 to 5. Using a Quick Calculation you can easily add a ‘Percent of Row’ calculation.

Inevitably, many people don’t leave a rating - here, that’s around half of orders. You could filter out the null ratings, but you might still be interested to see them (and it would spoil my example!)

By writing your own expressions for Table Calculations you can control exactly what’s included or not, and we can learn a bit about the different functions available and how they behave.

First we need to total up the orders which did have ratings. There are a couple of ways to approach this - you could use the pivot_index function to get the order count value for each pivoted column, by number, and add them up (except for the last column with the nulls). Notice that Looker will give you suggestions as you type, for both expression functions and available values in your results table. This expression is a bit verbose, though, and will also break if we change the order of the pivoted columns.

We could also use the row total that we have in our table, and subtract the nulls in the last column (index 6). This is much more concise but still depends on the column order, as well as the presence of the row total.

A more robust way to write this expression uses the pivot_row function to get all the values in a row, and the sum function to add them up. This is like the row total but means we don’t need to include the row total. Next, to get the number of orders without a rating, regardless of pivot column order, we can use the pivot_where function. This lets us use a ‘yes/no’ (i.e. boolean) function, and so long as it is only a ‘yes’ for a single column, it’ll work. We can use the is_null function to identify our null ratings column.

Now we have the number of orders with ratings, we can easily calculate both the % of all orders with any rating, and the % for each rating as a share of all orders with ratings.

Notice how the ‘% of Rated Orders’ calculation is repeated for each column - this is because Looker is aware of what functions should apply to each column, or to the row as a whole.

I hope this helps demystify, and show the power of, custom expressions in Looker’s Table Calculations!