home User Guide Getting Started Help Center Documentation Community Training Certification
menu
close
settings
Looker keyboard_arrow_down
language keyboard_arrow_down
English
Français
Deutsch
日本語
search
print
Using table calculations

add

New in Looker 7.20, the Looker expressions editor will display fields that are in use at the top of the suggestions list when you type a blank space in the editor; as you refine your search results, each field in use will be indicated by a black circle. This makes it easier to identify and select fields to use in on-the-fly analyses with table calculations and custom fields.

Table calculations make it easy to create on-the-fly metrics. They are similar to formulas found in spreadsheet tools like Excel. Table calculations appear as green columns in the data table, rather than as blue columns (dimensions) or orange columns (measures).

The last column in the table below uses a table calculation to combine three fields in the data using the concat function.

Table calculations can perform mathematical, logical (true/false), lexical (text-based), and date-based calculations on the dimensions, measures, and other table calculations in your query. The formulas that you use to execute these calculations are called Looker expressions.

Table calculations are different from regular fields

Although table calculations are similar to dimensions and measures, there are some important differences:

Creating table calculations in Looker

Looker’s Explore page has a built-in Looker expression editor to help you create table calculations, custom fields, and custom filters.

Before you create a table calculation, make sure that all the fields you want to use in the table calculation have been selected from the field picker, and that you have run the query.

If you are a Looker developer creating a data test to verify the logic of your model, you can also use the Looker expression editor to build a Looker expression, then copy the expression into your data test’s expression parameter.

There are two possible ways to access the Looker expression editor from the Explore page:

In the Table Calculations pop-up window, you can start constructing your custom metrics. The expression you create can evaluate to a number, date, string (text), or Boolean (true/false).

If you already have some table calculations defined, click the Add Table Calculation button to create another. You are able to add as many table calculations as you need.

Then, for each table calculation:

  1. Rename your table calculation if desired.
  2. Optionally, click Default Formatting to choose a predefined format or create a custom format for the results. If you create a custom format, use Excel-style formatting as described on the Adding Custom Formatting to Numeric Fields documentation page.
  3. Start typing a Looker expression into the large text box to form your calculation. Looker expressions can be quite simple, or they can use as many fields, functions, and operators as your business logic requires.

    The Creating Looker Expressions documentation page explains how to create Looker expressions and how the editor helps you.

  4. If you are finished adding table calculations, click Save Table Calculations.

Your table calculation fields appear next to your dimensions and measures in the table. If you want to reuse your table calculations in the future, be sure to save your Look or copy the table calculation formula into another document.

Sorting table calculations

To sort on a table calculation, click the field name at the top of the column, just as you would a dimension or measure.

When table calculations cannot be sorted

Sorting on a table calculation works similarly to sorting on a dimension or measure in Looker. However, there are two important differences that prevent sorting in some scenarios:

The specific scenarios when you can’t sort a table calculation are:

  1. Calculations that hit a row limit, as described below.
  2. Sorting a dimension or measure after you’ve already sorted by a table calculation, as described below.
  3. Sorting a table calculation that makes use of an offset, as described below.

Calculations that hit a row limit

If the number of rows in your query exceeds the row limit that you’ve set, you will not be able to sort table calculations. This is because table calculations are only based on the rows that are displayed. Therefore, if you hit a row limit, the table calculation might be missing some rows that it should be sorting into your results. If you run into this issue, you can try increasing your row limit (up to 5,000 rows).

For example, the table below displays the 10 top-selling categories in an e-commerce store, sorted by total sales. Notice that the 10-row limit has been reached, which you’re warned about by the yellow bar displayed at the top of the table:

However, if you want to show the top 10 categories by number of orders instead, you can see the results change:

Sorting a dimension or measure after sorting a table calculation

As indicated above, table calculations are only based on the rows that are displayed. In contrast, sorting by a dimension measure goes back to your database to make sure it finds the correct rows. As a result, you should start sorting with dimensions and measures. Then, when the correct data has been returned from your database, you can sort those results based on a table calculation.

Calculations using an offset function cannot be sorted

Any table calculation that makes use of an offset cannot be sorted, because the sort order of the rows would change the results of the offset.

For example, below is a table calculation that displays the percentage change in weekly sales for the Accessories category:

This only makes sense if the results are sorted by the week.

Using table calculations in visualizations

Just like regular dimensions and measures, table calculations are automatically displayed in visualizations.

In addition, you can use table calculations to decide which rows of your data should be displayed in a visualization. The example we’ll use to explore this feature is shown below, and includes weekly sales information about the Accessories category. Note that the underlying data table includes the dimension Orders Created Week, the measure Total Sales, and a table calculation called Percent of Previous Week Sales that compares the revenue of each week against the previous week:

You can now prevent certain rows of data from showing up in the column chart. To do so, you’ll create a table calculation that evaluates to true or false, then hide the false values (which will appear as “No” entries in your data table). You don’t want the formula to result in the word “true” or “false”; rather, it should be a condition that is either true or false.

To achieve this, you could create a table calculation, Exceeds Previous Week Sales, that evaluates whether the Percent of Previous Week Sales calculation is greater than 1:

This will result in a table that includes a new table calculation that evaluates each row against the Exceeds Previous Sales calculation, and displays a yes or a no depending upon whether the percent of previous is greater than 1:

To hide all of the rows where a particular week’s revenue did not exceed the revenue of the previous week, click the gear icon at the top left of the logical calculation and select Hide “No”s from Visualization:

The resulting visualization will now display only the weeks that exceeded the previous week’s revenue:

One common use case for this feature is hiding the first or last row from a visualization, since many types of analyses create bad rows at the beginning or end of a table. For example, when you are calculating running totals, have a partial day ending a date analysis, or, like the example below, when you are calculating a percent of the previous row:

To get rid of that row, simply create a new table calculation to filter out the null value by using the is_null logical function:

Then, hide the row:

Considerations for using table calculations

Conclusion

Table calculations provide a powerful way for any Looker user to manipulate and analyze data, without having to create new LookML fields. Next, you’re ready to go deeper into using Looker expressions in table calculations and custom filters.

Top