In this blog post, we will explore the exciting multi-row calculations that you can now perform in Tableau Prep. This feature was introduced in the latest release of Tableau (version 2023.2) and is a powerful addition to your data wrangling toolkit. We'll walk you through how to use this feature, and provide a detailed explanation of the available functions. Let's get started!

Setting up the Flow

First, let's set up a flow to demonstrate the multi-row calculations. We will use a simple data set with three fields: Order Date, Category, and Quantity. The Order Date has been truncated to the month, while the Category field contains three categories from the Superstore data set. The Quantity field will be the one we use for our calculations.

You can find the starting flow file here.

Using Multi-Row Calculations in the Clean Step

The multi-row calculations are available in the Clean step of Tableau Prep. To access these functions, click on the three dots next to the field you want to compute the multi-row calculations for, in our case, the Quantity field. You will see a list of custom calculations, as well as the three moving calculations that we're interested in:

1. Difference From
2. Percent Difference From
3. Moving Calculation (AVG & Sum)

These are technically multi-row calculations because they require looking at multiple rows of data to perform the calculations.

It's worth noting that multi-row calculations have been possible in earlier versions of Tableau using LODs (Level of Detail) expressions or even scripting. However, the new interface in Tableau Prep makes it much easier to set up these calculations.

Now, let's dive into each of these calculations and explore how they work.

Moving Calculation

When you select the "Moving Calculation" option, a new interface will appear, showing a preview of the calculation and allowing you to configure it.

Here's a breakdown of the interface:

• On the left, you will see a visual summary of the calculation outcome.
• In the middle, you have several options to configure the calculation:
• Group By: This will group the calculation by the selected field (in our case, Category).
• Order By: This will order the data within the group (in our case, Order Date, by month).
• Field: This is the field used for the calculation (in our case, Quantity).
• Computation: This is how the calculation is performed (either Sum or Average).

Additionally, there is a drop-down option that allows you to configure how many rows to look back or forward in the calculation. This option includes the current row by default, so if you set it to look back one row, it will include the current row and the previous row in the calculation.

After configuring the calculation, you will notice that Tableau Prep visually displays the moving window in the data preview. This is an excellent way to understand and validate how the calculation is changing as it moves through the data set.

The moving calculation interface is a significant improvement over having to manually write the calculations in previous versions of Tableau.

Difference From and Percent Difference From

Similar to the moving calculations, the "Difference From" and "Percent Difference From" calculations also provide a configurable user interface.

The difference between these and the moving calculations is that these will calculate the difference or percent difference between the current row and a specified number of rows back or forward.

As with the moving calculations, Tableau Prep visually displays the difference calculations in the data preview, allowing you to easily validate and understand how the calculations are working.

Leveraging Multi-Row Calculations for Data Modeling

Multi-row calculations in Tableau Prep can be a powerful tool to enhance your data modeling experience in Tableau Desktop.

By using these calculations in Tableau Prep, you can pre-compute values such as moving averages, differences, or percent differences, making it easier and faster to work with your data in Tableau Desktop. This can be particularly useful when working with large data sets or creating complex data models that require numerous calculations.

It's essential to consider when and where to use multi-row calculations, as some specific use cases might be better suited to performing calculations in Tableau Desktop. For example, running totals or dynamically calculated values may be better suited to be computed directly in Tableau Desktop.

With the continued improvements to the Tableau data model, multi-row calculations in Tableau Prep will enable you to build more flexible and nuanced data models, allowing you to answer more complex questions with your data.

Wrapping Up

In this blog post, we have explored the new multi-row calculations feature in Tableau Prep. We have provided detailed explanations and examples of using these calculations, and discussed the benefits of using them in your data modeling experience.

We hope you found this post informative and helpful for leveraging multi-row calculations in your Tableau journey! Remember to subscribe to our blog for more detailed and informative content on Tableau and data analysis.

‍
Timestamps
0:00 Intro
0:24 Setting up the flow
1:53 Creating multi-row calculations
8:56 Use cases for this feature
‍