Calculate Values Across Multiple Rows

Supported in Tableau Prep Builder version 2023.2 and later and on the web in Tableau Cloud. This feature is not yet supported in Tableau Server.

Note: Starting in version 2020.4.1, you can create and edit flows in Tableau Server and Tableau Cloud. The content in this topic applies to all platforms, unless noted. For more information about authoring flows on the web, see Tableau Prep on the Web in the Tableau Server(Link opens in a new window) and Tableau Cloud(Link opens in a new window) help.

Multi-row calculations let you compute values between multiple rows of data in your flow. While similar to table calculations in Tableau, multi-row calculations apply to your entire data set when you run your flow. You can also build on the result using other types of calculations.

In Tableau, table calculations only apply to values in your visualization. While you can build on the result, you must use another table calculation to do so. For more information about using table calculations in Tableau, see Transform Values with Table Calculations(Link opens in a new window) in the Tableau help.

Performing table calculations during data preparation can provide greater flexibility when analyzing data in Tableau. You can easily reuse the calculation when building your view and the underlying calculation isn't impacted by filtering. Workbook load times for large data sets can be faster as the table calculation isn't recalculated after the query runs.

Tableau Prep currently supports the following multi-row calculations:

Difference from: Computes the difference between the current row value and another value.
Percent difference from: Computes the difference between the current row value and another value as a percentage.
Moving calculations: Returns the sum or average of a numeric field within a flexible set of rows.

Use the visual calculation editor to quickly generate the calculation, or write your own custom calculation in the calculation editor using the LOOKUP() function.

Calculate Difference From

A Difference From calculation computes the difference between the current value and a value N rows before or after the current row.

Visual calculation editor

Select fields from a list and Tableau Prep builds the calculation for you as you make your selections. A preview of the new field results is shown in the left pane and you can review the calculation results in the far right of the pane.

In a profile card or results pane, click the More options menu and select Create Calculated Field > Difference From.
In the Group by section, select the fields with rows that you want to include in the calculation. This partitions your table when performing the calculation. To apply the calculation to all rows in the table, accept the default value Full table.

After you select your first field, click the plus icon to add any additional Group by fields to your partition. To reorder or remove fields, right-click or Ctrl-click (MacOS) and select an action from the menu.
In the Order by section, select the fields that you want to use as the sort order. This field is used to specify how the LOOKUP function orders the rows in your table.

If the field where you selected the Create Calculated Field >Difference From menu option is a date or time field, then this field is added by default, but you can change it.

Click the plus icon to add any additional Order by fields to your calculation. Click the sort icon to change the order from ascending (ASC) to descending (DESC). You can also right-click or Ctrl-click (MacOS) and select an action from the menu to reorder or remove fields.
In the Compute using section, select the field with the values that you want to use to calculate your results.
In the Difference From section, select the rows to use to calculate the difference. For example select Previous Value, 2 to calculate the difference between the current value and a value 2 rows before that value. Annotations highlight the rows used to perform the calculation.

By default, the calculation preview will show you the first non-null row. However, you can click on any row in the results table and see an updated preview of the selected value.

If the calculation can't be performed with the current settings, the annotation Not enough values is shown. To resolve this issue, either select a different current value or change the configuration in the Difference From section.
In the left pane, double-click in the field header and enter a name for your calculation.
Click Done to add your new calculated field. In the Changes pane, you can see the calculation that Tableau Prep generated. Click Edit to open the visual calculation editor to make any changes.

Calculation editor

If you want to write your own calculation to calculate the difference between two values, use the LOOKUP function in the Calculation editor.

In the Profile pane toolbar click Create Calculated Field, or in a profile card or data grid, click the More options menu and select Create Calculated Field > Custom Calculation.
In the Calculation editor, enter the expression. For example, to find the difference between current sales and the previous day's sales by region, create a calculation like the one shown below.
```
{PARTITION [Region]:{ ORDERBY [Order Date]ASC:LOOKUP([Sales],0)}}
-
{ PARTITION [Region]:{ ORDERBY [Order Date]ASC:LOOKUP([Sales],1)}}
```
Enter a name for your calculation, and click Save.

Calculate Percent Difference From

A Percent Difference From calculation computes the difference between the current value and a value N rows before or after the current row as a percentage. For example Value1-Value2/Value2.

Visual Calculation editor

In a profile card or results pane, click the More options menu and select Create Calculated Field > Percent Difference From.
In the Group by section, select the fields with rows that you want to include in the calculation. This partitions your table when performing the calculation. To apply the calculation to all rows in the table, accept the default value Full table.

After you select your first field, click the plus icon to add any additional Group by fields to your partition. To reorder or remove fields, right-click or Ctrl-click (MacOS) and select an action from the menu.
In the Order by section, select the fields that you want to use as the sort order. This field is used to specify how the LOOKUP function orders the rows in your table.

If the field where you selected the Create Calculated Field > Percent Difference From menu option is a date or time field, then this field is added by default, but you can change it.

Click the plus icon to add any additional Order by fields to your calculation. Click the sort icon to change the order from ascending (ASC) to descending (DESC). You can also right-click or Ctrl-click (MacOS) and select an action from the menu to reorder or remove fields.
In the Compute using section, select the field with the values that you want to use to calculate your results.
In the Percent Difference From section, select the rows to use to calculate your result. For example select Previous Value, 2 to calculate the percent difference between the current value and a value 2 rows before that value. Annotations highlight the rows used to perform the calculation.

By default, the calculation preview will show you the first non-null row. However, you can click on any row in the results table and see an updated preview of the selected value.

If the calculation can't be performed with the current settings, you will see the annotation Not enough values. To resolve this, either select a different current value or change the configuration in the Percent Difference From section.
In the left pane, double-click in the field header and enter a name for your calculation.
Click Done to add your new calculated field. In the Changes pane, you can see the calculation that Tableau Prep generated. Click Edit to open the visual calculation editor to make any changes.

Calculation editor

If you want to write your own calculation to calculate the percent difference between two values, use the LOOKUP function in the Calculation editor.

In the Profile pane toolbar click Create Calculated Field, or in a profile card or data grid, click the More options menu and select Create Calculated Field > Custom Calculation.

In the Calculation editor, enter the expression. For example, to find the percent difference between current sales and previous days sales by region, create a calculation like the one shown below.

{ PARTITION [Region]:{ ORDERBY [Order Date]ASC:LOOKUP([Sales],0)}}
-
{ PARTITION [Region]:{ ORDERBY [Order Date]ASC:LOOKUP([Sales],-1)}}
/
{ PARTITION [Region]:{ ORDERBY [Order Date]ASC:LOOKUP([Sales],-1)}}

Enter a name for your calculation, and click Save.

Calculate Moving Average or Sum

Create a moving calculation to better understand trends in your data and reduce overall fluctuations. In Tableau Prep you can calculate a moving average or sum across a specified number of values before or after the current value. For example tracking the three month moving average of sales per region.

Visual Calculation editor

In a profile card or results pane, click the More options menu and select Create Calculated Field > Moving Calculation.
In the Group by section, select the fields with rows that you want to include in the calculation. This partitions your table when performing the calculation. To apply the calculation to all rows in the table, accept the default value Full table

After you select your first field, click the plus icon to add any additional Group by fields to your calculation. To reorder or remove fields, right-click or Ctrl-click (MacOS) and select an action from the menu.
In the Order by section, select the fields that you want to use as the sort order. This field is used to specify how the LOOKUP function orders the rows in your table.

If the field where you selected the Create Calculated Field > Moving Calculation menu option is a date or time field, then this field is added by default, but you can change it.

Click the plus icon to add any additional Order by fields to your calculation. Click the sort icon to change the order from ascending (ASC) to descending (DESC). You can also right-click or Ctrl-click (MacOS) and select an action from the menu to reorder or remove fields.
In the Compute using section, select the field with the values that you want to use to calculate your results.
In the Results section, select the aggregation you want to perform (sum or average), the number of rows to include in the calculation, and whether to include the current row or exclude it.

To change the results setting, click the drop-down in the Values field. For example, to calculate the moving average of sales across the current month and previous 2 months, set the Previous values to 2 and close the dialog.
By default, the calculation preview will show you the first non-null row. However, you can click on any row in the results table and see an updated preview of the selected value. Annotations highlight the rows used to perform the calculation.

If the calculation can't be performed with the current settings, you will see the annotation Not enough values. To resolve this, click the drop-down in the Values field to change the configuration in the Results Settings.
In the left pane, double-click in the field header and enter a name for your calculation.
Click Done to add your new calculated field. In the Changes pane, you can see the calculation that Tableau Prep generated. Click Edit to open the visual calculation editor to make any changes.

Calculation editor

If you want to write your own calculation to calculate the moving average or sum, use the LOOKUP function in the Calculation editor.

In the Profile pane toolbar click Create Calculated Field, or in a profile card or data grid, click the More options menu and select Create Calculated Field > Custom Calculation.
In the Calculation editor, enter the expression. For example, to find the three month moving average of sales per region, create a calculation like the one shown below.

Note: This example assumes that the data set is at the correct level of detail, one row for each month. If your data set is not at the correct level of detail, consider using an aggregation step to change this before applying the calculation.
```
{ PARTITION [Region]:{ ORDERBY [Year of Sale]ASC,[Order Month]ASC:LOOKUP([Sales],-2)}}
+
{ PARTITION [Region]:{ ORDERBY [Year of Sale]ASC,[Order Month]ASC:LOOKUP([Sales],-1)}}
+
{ PARTITION [Region]:{ ORDERBY [Year of Sale]ASC,[Order Month]ASC:LOOKUP([Sales],-0)}}
/
3
```
Enter a name for your calculation, and click Save.

Get Previous Value

If you need to create a field with the value from a previous row, you can create a custom calculation using the LOOKUP function.

In the Profile pane toolbar click Create Calculated Field, or in a profile card or data grid, click the More options menu and select Create Calculated Field > Custom Calculation.
In the Calculation editor, enter the expression. For example, to find the previous sales value by order date, create a calculation like the one shown below.

Note: This example assumes that the data set is at the correct level of detail, one row for each day. If your data set is not at the correct level of detail, consider using an aggregation step to change this before applying the calculation.
```
{ ORDERBY [Order Date]ASC:LOOKUP([Sales],-1)}
```
Enter a name for your calculation, and click Save.

Thanks for your feedback!

Tableau Prep Help

Calculate Values Across Multiple Rows

Calculate Difference From

Visual calculation editor

Calculation editor

Calculate Percent Difference From

Visual Calculation editor

Calculation editor

Calculate Moving Average or Sum

Visual Calculation editor

Calculation editor

Get Previous Value