Table Calculations

Table calculations make it easy to create metrics on the fly (for example, aggregating the data in your table over some window, or getting a running total of a column). You add table calculations in the Explore view and they appear as green columns in your results table (remember, dimensions are blue, and metrics are orange).

Table calculations are built using raw SQL. That means you can use table calculations to build mathematical, True/False, text, and date-based calculations (basically, anything you can do in SQL, you can do in table calculations).

When to use table calculations

Typically, in your Lightdash project, you'll have one or more Tables that you've pre-defined in your dbt project (this is probably done by the data analysts/engineers/analytics engineers). But sometimes, you'll need particular logic which hasn't been defined as a dimension or metric in your dbt project - maybe because you have a new kind of question or use case.

This is when you might need a table calculation.

Watch out: table calculations can be easier to create than regular metrics/dimensions, but they are not as easy to manage. If you find yourself creating the same table calculation over and over again when you're using a Table, then it might be worth adding it in as a more permanent metric in your dbt project! You can read more about adding metrics to your dbt project here.

Creating table calculations

Before you create a table calculation, you need to add some dimensions and/or metrics to your results table.

Table calculations can only be built using the dimensions + metrics that you've included in your results table. So, to create a table calculation, first, you need to add the dimensions and/or metrics for your table calculation to your results table.

Using Quick Calculations

Quick calculations are shortcuts to the most common table calculations, these are only available to metrics in the contextual menu in the results table.

To learn more about what these calculations are doing, check out the docs here. Once the table calculation is generated you can edit it to modify the SQL query or update the format.

Using Custom Table Calculations

To add a custom table calculation just click on + Table calculation in the Results tab

Once you've got some data in your results table, you can create a table calculation by clicking on the + Table calculation in the Results tab of the Explore view:

3. Write the SQL for your table calculation in the pop-up box

Your table calculation is defined using raw SQL that you write up in this pop up box. If you're not sure what to write here, you can check out some of our table calculation SQL templates.

To reference the metrics and dimensions in your results table, you can either use the autocomplete, or you can manually write the full field name using the format ${table_name.field_name}.

4. Update the format of your table calculation (if needed)

If needed, you can update the format of your table calculation to things like percent formatting using the format tab.

Format types	Description	Raw value	How it might look like in Lightdash
Default	Default format for the table calculation result	0.75	0.75
Percent	Converts numbers into a percentage	0.75	75%
Currency	Adds currency symbol and default locale format	1234.1234	$ 1234.12
Number	Formats number with prefix and suffix	123.1234	Speed: 123.12 km/h

5. To delete or edit your table calculation, just click on the gear icon by the field name

If you need to edit or delete a table calculation, you can just click on the toggle beside the field and do what you need to do!