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

Table calculations make it easy to create ad hoc metrics. They are similar to formulas that are 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 following table 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:

Table calculation fields appear next to 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.

Creating table calculations

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, depending on whether your Looker instance is or is not enabled for custom fields.

Adding table calculations with custom fields enabled

If your Looker instance is enabled for custom fields and you have the permissions to use them, open the Looker expression editor with the following steps:

  1. Open the Custom Fields section of the field picker.
  2. Click Add.
  3. Select Table Calculation.

Then, for each table calculation:

  1. Select a calculation type from the Calculation drop-down. The options for a Custom expression are displayed by default.
  2. Add the calculation definition, including selecting a Quick Calculation’s Source field, as desired. Only numeric fields that are selected in the Explore’s data table are eligible for calculation types other than Custom expression.
    • If Custom expression is selected from the Calculation drop-down, start typing a Looker expression in the large text box to create your calculation. You can only create table calculations from fields that are selected in the Explore’s data table. Looker expressions can be quite simple; or they can use as many fields, functions, and operators as your business logic requires. The expression you create can evaluate to a number, date, string (text), or Boolean (true/false).
    • The Creating Looker expressions documentation page explains how to create Looker expressions and how the editor can assist you.
  3. Select a format other than the default from the Format drop-down, if desired.
  4. Enter a new calculation name other than the default as desired. The calculation name appears in the field picker and in the data table.
  5. Click + Add Description to add an optional description of up to 255 characters that can give other users more context or information about the table calculation.
  6. Click Save.

The new calculation will automatically appear in the data table and in the Custom Fields section of the field picker. As with other fields, you can click the calculation’s name to add or remove it from a query. You can also hover over the field to reveal more options that are available for that calculation.

Adding table calculations without custom fields enabled

If your Looker instance is not enabled for custom fields, click the Calculations button from the Data bar:

In the Edit Table Calculation pop-up, construct 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 from the default name, if desired. The calculation name appears in the field picker and in the data table.
  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. If no selection is made, Looker uses a default format.
  3. Start typing a Looker expression in the large text box to form your calculation. You can only create table calculations from fields that are selected in the Explore’s data table. Looker expressions can be quite simple; or they can use as many fields, functions, and operators as your business logic requires. The expression you create can evaluate to a number, date, string (text), or Boolean (true/false).

    The Creating Looker expressions documentation page explains how to create Looker expressions and how the editor can assist you.

  4. If you are finished adding table calculations, click Save to add the calculation to the Explore.

Quick Calculations

If your Looker instance is enabled for custom fields and you have the permissions to use them, you can use Quick Calculations to perform common calculations on numeric fields that are in an Explore’s data table without needing to use Looker functions and operators.

The types of calculations available with Quick Calculations include the following:

When Explore results are pivoted, the following calculation types are also available for pivoted measures:

The order in which an Explore’s data table is sorted can impact calculations that are based on a value’s position relative to other values in a column, such as Percent of previous, Percent change from previous, Percent of previous column, Percent change from previous column, and Percent of row. Make sure an Explore’s data table is sorted accordingly.

You can perform a Quick Calculation on numeric fields that are in an Explore’s data table, including custom measures, custom dimensions, and other table calculations, in one of the following ways:

Using a field’s three-dot Options menu to create a Quick Calculation

In many cases, you can use this shortcut technique to perform Quick Calculations on numeric fields:

  1. Expand the view that contains the Explore field on which you want to perform a calculation. In this example, the user chooses to perform a calculation on the Inventory Items Count measure.
  2. You can perform the next step in two different ways:
    • Expand the view, click the dimension’s three-dot Options menu, and hover your cursor over the Quick Calculations menu to display the available calculation types.
    • If the field is already selected in an Explore, you can hover your cursor over the Quick Calculations option in the dimension’s gear menu in the Data table to display the available calculation types.
  3. Select a calculation type. This example uses Percent of previous to compare the count of inventory items with the previous month’s count of inventory items.
    • Pivot calculation types will not appear in a measure’s three-dot Options menu or data table gear menu unless Explore results are pivoted.

The new calculation will automatically appear in the data table:

The new calculation will also appear in the Custom Fields section of the field picker. As with other fields, you can click the calculation’s name to add or remove it from a query. You can also hover over the field to reveal more options that are available for that calculation, including editing the calculation.

Using the Custom Fields section to create a Quick Calculation

If you want to select a format or name other than the default, or add a description while creating a Quick Calculation, start with the Add button on The Custom Fields section:

  1. Open the Custom Fields section of the field picker.
  2. Click Add.
  3. Select Table Calculation.

  1. Select a calculation type. This example uses Percent of previous to compare the count of inventory items with the previous month’s count of inventory items.
  2. Select the field on which you want to perform the calculation. Only numeric fields that are selected in the Explore’s data table will be available to choose from. In this example, the user chooses to perform a Percent of previous calculation on the Inventory Items Count measure.
  3. Optionally, click the Format drop-down 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. If no selection is made, Looker uses a default format.
  4. Rename your table calculation from the default name, if desired. The calculation name appears in the field picker and in the data table.
  5. Click + Add Description to add an optional description of up to 255 characters that can give other users more context or information about the table calculation.
  6. If you are finished adding table calculations, click Save to add the calculation to the Explore.

The new calculation will automatically appear in the data table:

The new calculation will also appear in the Custom Fields section of the field picker. As with other fields, you can click the calculation’s name to add or remove it from a query. You can also hover over the field to reveal more options that are available for that calculation, including editing the calculation.

Duplicating table calculations

If the Custom Fields Labs feature is enabled for your instance, and you’re allowed to create a table calculation, you can also duplicate existing table calculations. Duplicating and then editing table calculations can be helpful if you’d like to create multiple table calculations with only small differences (for example, 30-day, 60-day, or 90-day sums).

To duplicate a table calculation:

  1. In the field picker, expand the Custom Fields section.
  2. Click the three-dot Options menu for the table calculation that you want to duplicate.
  3. Select Duplicate.

The duplicated table calculation appears below the original, using the name of the original table calculation plus the word “Copy” appended to the end:

Next, you can edit the duplicated table calculation.

Editing table calculations

If you have the permissions to create table calculations, you also can edit existing table calculations that you or other users have created.

There are two possible ways to edit table calculations, depending on whether your Looker instance is or is not enabled for custom fields:

Editing table calculations with custom fields enabled

If your Looker instance is enabled for custom fields and you are able to create table calculations, you can use the field picker to edit table calculations. Additionally, you can use the field’s gear menu in the Explore data table.

To edit a table calculation with the field picker:

  1. Expand the Custom Fields section.
  2. Click the three-dot Options menu for the table calculation that you want to duplicate.
  3. Select Edit.

  1. Select a new calculation type from the Calculation drop-down, if desired.
  2. Change the calculation definition, including a Quick Calculation’s Source field, as desired. Only numeric fields that are selected in the Explore’s data table are eligible for calculation types other than Custom expression.
    • If Custom expression is selected from the Calculation drop-down, either add a Looker expression to, or edit an existing Looker expression in, the large text box. You can only create table calculations from fields that are selected in the Explore’s data table.
  3. Select a new format from the Format drop-down, if desired.
  4. Enter a new calculation name, as desired. The calculation name appears in the field picker and in the data table. If you have changed anything on a table calculation, consider modifying the name to match.
  5. Add or update an optional field description of up to 255 characters with details about the table calculation, including its intended use.
    • If there is an existing description, the Description box will automatically appear. If there is no existing description, click + Add Description to add an optional description.
  6. Click Save.

Editing table calculations without custom fields enabled

To edit a table calculation when custom fields are not enabled:

  1. Select Edit from the field’s gear menu.

  1. Add an additional table calculation, if desired.
  2. Enter a new field name, as desired. If you change anything on a table calculation, consider modifying the name to match.
  3. Select a new format from the Format drop-down, if desired, or click Remove to remove any specified formatting.
  4. Change the table calculation definition, as desired. You can only create table calculations from fields that are selected in the Explore’s data table.
  5. Click Save.

Deleting table calculations

If you have the permissions to create table calculations, you also can delete table calculations that you or other users have created. When you delete a table calculation, it disappears from the Explore but not from any Looks or dashboard tiles that use that field. Also, anyone using a URL for an Explore that had the custom field will still have the field.

There are two possible ways to delete table calculations, depending on whether your Looker instance is or is not enabled for custom fields.

Deleting table calculations with custom fields enabled

If your Looker instance is enabled for custom fields and you are able to create table calculations, you can use the field picker to delete table calculations. Additionally, you can use the method using a field’s gear menu in the Explore data table.

To delete a table calculation with the field picker:

  1. Expand the Custom Fields section.
  2. Click the three-dot Options menu for the table calculation that you want to delete.
  3. Select Delete.

You can also use the keyboard shortcuts Command-K (Mac) or Ctrl+K (Windows) to delete custom fields.

You can reinstate a custom field that you’ve deleted by clicking the “back” arrow on your browser.

Deleting table calculations without custom fields enabled

To delete a table calculation when custom fields is not enabled:

  1. Select Remove from the field’s gear menu.

You can also use the keyboard shortcuts Command-K (Mac) or Ctrl+K (Windows) to delete custom fields.

You can reinstate a custom field that you’ve deleted by clicking the “back” arrow on your browser.

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:

Specific scenarios where you can’t sort a table calculation include the following:

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 following table 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, the results would look like this:

Sorting a dimension or measure after sorting a table calculation

As indicated in the Calculations that hit a row limit section on this page, table calculations are only based on the rows that are displayed. In contrast, sorting by a dimension or a 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 that use 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, the following table calculation 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. We’ll use the following example to explore this feature; this example includes weekly sales information about the Accessories category. Note that the underlying data table includes the dimension Orders Created Week and the measure Total Sale Price, along with a table calculation called Percent of Previous Week Sales, which 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:

${percent_of_previous_week_sales} > 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 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, you might decide to hide the first or last row when you are calculating running totals, when you have a partial day that ends a date analysis, or, like the following example, 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:

NOT is_null(${percent_of_previous_week_sales})

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