If you have used Tableau Prep then the lack of multi-row calculations in Tableau Prep has been a frustration for a while. The lack of this feature often leads to “hacks” to simulate the feature. Luckily Tableau Prep now has multi-row calculations in version 2023.2. Let’s talk about using this feature. Just a note that, Tableau Prep is calling multi-row calculations “Moving Calculation”, so look for that name in Prep.
Use Cases for Multi-Row Calculations (“Moving Calculations”):
Data Modeling
Reduce the need to create performance-intensive calculations in Tableau Desktop. With Moving Calculations, you can shape your data in Tableau Prep to include moving averages, sums, or differences.
Automation:
Moving calculations are key for the automation of workflows, this feature ensures that data transformations are consistently applied across the dataset. Automation relies on consistent rules and operations, and moving calculations provide a standardized approach to data manipulation.
Simplified Interactivity:
Once your data is exported from Tableau Prep to Tableau Desktop, your workbook filters won’t change how your table calculation is computed.
Here is the example I am using, which utilizes the Superstore dataset:
Setting up the Prep Flow:
In the Tableau Prep flow, I'm using three important fields: order date (truncated to the month), region (with four regions from the Superstore dataset), and profit. The profit field is the most important for this example since we are using it for our calculations.
Creating Moving Calculations:
Just a note, the moving calculations feature is accessed through the “clean step” in Tableau Prep.
Here are the key steps:
Aggregation Step
First, you should group your calculations. For this example, we are grouping by region and date month of order date and summing by profit.
Clean Step: Moving Calculation:
Specify what calculation you want to perform within the defined group. In this example, we are grouping by region and date month and we are using a moving sum, which adds the previous row, the current row, and the next row of profit.
In this example, I have configured the Moving Calculation to look at the previous month but this can be adjusted to suit your needs. See below:
This is a closer look at the calculation:
Visual Cues:
A cool feature that has been included in Tableau Prep is what can be described as visual cues. As you adjust your configuration for Moving Calculation and go through the data, Tableau Prep creates a subtle dotted blue highlight box showing how the calculation is working as it is performed for each row.
Multi-row calculations or Moving Calculations offer an exciting new dimension to your data preparation and modeling capabilities. This feature is powerful on its own and allows carrying out a function that was only native to a Tableau Desktop before, essentially you are now able to shape your data before importing it to Tableau Desktop. This is definitely a feature that makes Tableau Prep stronger.