Calculate Values Across Multiple Rows

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 results using other types of calculations.

Note: When creating a calculated field, don't use the reserved name "Number of Records". Using this reserved name will result in a permission-related error.

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.
Fill Down calculations: Computes and fills in missing data from the rows above.
Running calculations: Computes the sum or average of values in a column.

Use the visual calculation editor to quickly generate the calculation, or write your own custom calculation in the calculation editor.

Calculate Difference From

Supported in Tableau Prep Builder 2023.2 and later and on the web in Tableau Cloud. Also supported in Tableau Server 2023.3 and later.

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

Supported in Tableau Prep Builder 2023.2 and later and on the web in Tableau Cloud. Also supported in Tableau Server 2023.3 and later.

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

Supported in Tableau Prep Builder 2023.2 and later and on the web in Tableau Cloud. Also supported in Tableau Server 2023.3 and later.

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 the previous 2 months, set the Previous values to 2 and close the dialog.
By default, the calculation preview shows 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

Supported in Tableau Prep Builder 2023.2 and later and on the web in Tableau Cloud. Also supported in Tableau Server 2023.3 and later.

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.

Fill Down Calculation

Supported in Tableau Prep Builder 2023.2 and later and on the web in Tableau Cloud. Also supported in Tableau Server 2023.3 and later.

A Fill Down calculation computes and fills in missing data so that null values are replaced with the values from the rows above, allowing for the data to be easily visualized. The example below shows the results of a fill down calculation by replacing the null values from the Student field with values based on the rows above.

Prep Visual calculation editor

In a profile card or results pane, click the More options menu, and select Create Calculated Field > Fill Down.

In the Group by section, you can select fields to partition the data when performing the calculation. If you don't want your data to be partitioned, accept the default value of Full table.

After you select your first field, you can add additional Group by fields to your partition by clicking the plus icon. To reorder or remove fields, right-click or Ctrl-click (MacOS) and select an action from the menu.

In the Order by section, select one or more fields that you want to use for the sort order. This field is used to specify the order of rows in a table.

You can also add a source row number in the input step of text, CSV, or Excel files to sort the data in the data source order.

Click the plus icon to add any additional Order by fields to your calculation. You must choose at least one field. 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 fill down. The Results field shows the filled down values in bold.
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

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.
Enter a name for your calculation, and click Save.

Examples

Fill down example with a partition: Based on the following data, you can fill down the missing values in the Sensor Category field using the LAST_VALUE function. In this example, Sensor ID is used to partition the data, Time Stamp is used to order the data, and Sensor Category is used to compute the data.

The fill down results is highlighted in the calculation pane.

Fill down example without a partition: Based on the following data, you can fill down the missing values of the Student field. The Source Row Number field has been added in an input step and is used to order the data in the source order. Student is used to compute the data.

The fill down results is highlighted in the calculation pane.

Running Calculation

Supported in Tableau Prep Builder 2023.2 and later and on the web in Tableau Cloud. Also supported in Tableau Server 2023.3 and later.

A Running Calculation computes the cumulative sum or average of a value and all previous values in the column. The example below shows the results of a running calculation for the sum of Sales over time.

Prep Visual calculation editor

In a profile card or results pane, click the More options menu, and select Create Calculated Field > Fill Down.

In the Group by section, you can select fields to partition the data when performing the calculation. If you don't want your data to be partitioned, accept the default value of Full table.

In the Order by section, select one or more fields that you want to use for the sort order. This field is used to specify the order of rows in a table.

You can also add a source row number in the input step of text, CSV, or Excel files to sort the data in the data source order.

Click the plus icon to add any additional Order by fields to your calculation. You must choose at least one field. 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 Results section, select the aggregation you want to perform (SUM or AVG), for a running sum or a running average
Click the rows in the results table to see an updated preview of the selected value. Annotations highlight the rows used to perform the calculation..
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

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, based on the following data, you can calculate the total of Sales over time using the RUNNING_SUM function. The data isn’t partitioned and is grouped using Full table. The Order field is used to sort in chronological order, and Sales is used to compute the data.
Enter a name for your calculation, and click Save.
The running sum results are highlighted in the calculation pane.

Thanks for your feedback!

Your feedback has been successfully submitted. Thank you!

Tableau Prep Help