Table of Contents



Overview

Table calculations provide a powerful way to derive new data within a data visualization without altering the underlying dataset. These calculations are performed on the result set after all other SQL calculations have been executed, allowing for dynamic and flexible analysis directly within report builder's interface. Table calculations are custom calculations created directly within report builder to perform calculations on data returned by a query. These calculations create the ability to compute new fields, metrics, or aggregations based on existing data in the result set, providing a high degree of flexibility for analysis.

When writing custom calculations using this tool, a wide range of mathematical, statistical, and conditional functions may be defined, and there can be a specific context entered for how these calculations may be performed. For example, calculations can be applied to a specific dimension, dimension group, or across entire data sets. Table calculations may help with viewing various analytical tasks such as year-over-year comparisons and trend analysis assisting in future decision making.

How to use Table Calculations

  1. To utilize table calculations in Report Builder, click the +Add button next to the Custom Fields dropdown on the left side panel of the report view.

  2. From this view, select Table Calculation.

  3. A window will appear to create a table calculation. A type of calculation must be selected to apply to the report. Below are the types of calculations that are selectable:

    Calculation TypeDefinition
    % of column

    The row value divided by the sum of values in the column. (Only includes values in the data table when row limit has been reached).

    % of previous row

    Current row’s value divided by the value of the row below.

    % change from previous row

    Difference between the current row’s value and value of the row below, divided by the value of the row below.

    Rank of Column

    Rank of row’s value among all values in the column (Only includes values in the data table when the row limit has been reached).

    Running column total

    Cumulative sum of this row and all previous rows in the column.

    Custom expression

    Add a custom expression, or edit an existing expression in the free field text box.




  4. Once the table calculation has been created, click Save to view the custom data in the report.


For more information on using table calculations in Report Builder, see these external sources: Using Table Calculations and Looker Functions and Operators.


Reporting Considerations

Row Limits and Table Calculations

  • Table calculations are applied to the rows returned by a query results. This means if a table calculation references Campaign ID, then Campaign ID needs to also be included in the query as a dimension (columns can subsequently be hidden from visualizations). If the query hits a row limit, the table calculation is only applied to those rows displayed.

  • Table calculations that are applied to the displayed query results affects their compatibility with some methods of generating and sharing query results. When getting results from Report Builder, whether via an ad-hoc query or a scheduled report, there is an option to include All Results instead of Results in Table. In order to use the All Results option, the query cannot contain table calculations.

For reporting API users: the way that the row limit is set varies slightly by endpoint:

  • For reporting/report-schedules/* endpoints: the parameter is send_all_results.

  • For reporting/run-query and /reporting/run-saved-report endpoints: the parameter is limit.

Table calculations will be returned when there is a row limit applied to the query, whether that is set via the API at query submission time (via the limit parameter) or set via the underlying Look’s row limit.

Other Considerations

The following are smaller considerations to keep in mind when creating a table calculation:

  • When adding a Total row to the data table, some table calculations that perform aggregations might not add up as expected. For example, this is the case for calculations that use percentile or median. This is because table calculations calculate totals using the values in the Total row, not by using the values in the data column.

  • If table calculation #1 is being referenced by table calculation #2 and the name of table calculation #1 is changed, table calculation #2 will return errors until the name is updated in its definition.