Table Calculations in Looker

Looker is a business intelligence and data analytics platform that allows users to perform table calculations. Table calculations are mathematical operations performed on columns in a data table to calculate new values or insights.

Here are some common table calculations in Looker:

1. Running Total: Calculates the cumulative total of a column of values up to the current row.
2. Difference: Subtracts the value of one column from another to calculate the difference between them.
3. Ratio: Divides the value of one column by another to calculate the ratio between them.
4. Moving Average: Calculates the average of a set of values over a specified number of preceding or following rows.
5. Percentage of Total: Calculates the percentage of a column’s value in relation to the total value of all values in the same column.
6. Percent Difference: Calculates the percentage difference between the value of one column and the value of another column.

To perform a table calculation in Looker, select the column(s) you want to apply the calculation to and then choose the calculation from the drop-down menu.

How to perform table calculations in Looker:

1. Open a Looker dashboard and select the data table you want to perform the calculation on.
2. Right-click on the column header and select “Create Calculated Field”.
3. In the “Create Calculated Field” dialog box, specify the calculation you want to perform. You can use Looker’s expression language, LookML, to specify the calculation.
4. Specify the name of the calculated field, and choose the data type for the result of the calculation.
5. Select the “Apply to Rows” option to perform the calculation for each row in the data table. If you select “Apply to Groups”, Looker will perform the calculation for each group of rows based on the grouping you have specified.
6. Click “Create” to save the calculated field. The calculated field will appear as a new column in the data table.
7. You can use the calculated field in visualization or export the results to another tool for further analysis.

It’s important to note that table calculations in Looker can be computationally intensive, so it’s recommended to limit the size of the data set you are working with or to aggregate the data to a lower level of detail before performing table calculations.

or you can also do the steps as mentioned in the picture

When to Use Table Calculations & When to Use Custom Fields

Examples of table calculations in Looker

Running Total: Let’s say you have a table with a column of sales data and you want to calculate the running total of sales for each row. The calculation would look like this in LookML

calculated_field: {
  type: sum
  sql: ${sales}
}

Difference: Let’s say you have a table with two columns: “Quantity Sold” and “Quantity Ordered”. You want to calculate the difference between the two columns to see how much inventory is left over. The calculation would look like this in LookML

calculated_field: {
  type: number
  sql: ${Quantity Ordered} - ${Quantity Sold}
}

Ratio: Let’s say you have a table with two columns: “Revenue” and “Costs”. You want to calculate the ratio of revenue to costs to see how profitable your business is. The calculation would look like this in LookML

calculated_field: {
  type: number
  sql: ${Revenue} / ${Costs}
}

Moving Average: Let’s say you have a table with a column of daily sales data, and you want to calculate the 3-day moving average of sales. The calculation would look like this in LookML

calculated_field: {
  type: average
  sql: ${sales}
  window_function: avg
  window_function_arg: 3
}

Percentage of Total: Let’s say you have a table with a column of sales data and you want to calculate the percentage of each sale in relation to the total sales. The calculation would look like this in LookML

calculated_field: {
  type: number
  sql: ${sales} / total(${sales})
}

Percent Difference: Let’s say you have a table with two columns: “This Year’s Sales” and “Last Year’s Sales”. You want to calculate the percent difference between the two columns to see how sales have changed year over year. The calculation would look like this in LookML:

calculated_field: {
  type: number
  sql: 100 * (${This Year's Sales} - ${Last Year's Sales}) / ${Last Year's Sales}
}

About Me:-
I am Om Prakash Singh – Data Analytics Consultant , Looker Consultant , Solution Architect .
I am Highly analytical and process-oriented Data Analyst with in-depth knowledge of database types; research methodologies; and big data capture, manipulation and visualization. Furnish insights, analytics and business intelligence used to advance opportunity identification.

You’ve got data and lots of it. If you’re like most enterprises, you’re struggling to transform massive information into actionable insights for better decision-making and increased business results.
Reach out to us here if you are interested to evaluate if Looker is right for you or any other BI solution.