Take a look at Tableau's latest release here: https://www.tableau.com/products/new-features
Tableau's latest release is here and with it comes a long-awaited feature: Multi-Row Calculations!
This is a capability that myself and Prep users have been looking forward to since Tableau Conference 2022! There are some easy-to-use calculations right out of the box along with some helpful visual aids with the visual calculation editor. The main use cases upon launch are:
Difference From
Percent Difference From
Moving Sum or Moving Average
Let's break each of theses down.
Difference From
The difference from calculation allows you to calculate the difference between a previous or upcoming value in the table. You can also choose to go multiple rows backwards or forwards. To get started with the Visual Calculation Editor, you can select the column you want to use, Sales in our case, and click on the ellipses on the right side. Go to Create Calculated Field and select Difference From.
The Visual Calculation Editor will pop up to allow easy configuration.
In this scenario, I want to see the difference in Category sales from the previous year. In order to do that, I'd use Category as my group by field and Year of Sales as my order by field because we are telling Tableau Prep "For each Category, order the data by Year of Sales". This will help Tableau Prep understand the order of the data so it knows what is previous and what is next. Since we want to understand the difference in Sales from the previous year, our Compute using field would be Sales, and the Difference From section would have Previous Value selected, and 1 as the value
My favorite part of this is how Prep uses visual aids to explain exactly what's happening. If we are looking at the Technology Category for the year 2016, our Difference From Previous Value would be -25,342 and Prep explains how it arrived at that value. The row containing 2016 sales is row B, the row containing 2015 sales is row A, so Prep is taking row B and subtracting row A to get the value.
We get a null value for row A because there is now previous row to compare to. Once our configuration is set, we can rename the calculation by double clicking on 'Calculation1'.
If we hover over the calculation text in the Changes box, we can see the way Prep wrote out the calculation
Here is the calculation written out:
{{PARTITION [Category]: ORDERBY [Year of Sale] ASC: LOOKUP([Sales], 0)}}
-
{{PARTITION [Category]: ORDERBY [Year of Sale] ASC: LOOKUP([Sales], -1)}}If we think about this compared to the visual editor, it breaks down like this:
The field in our Group By becomes the Partition
The field in our Order By becomes the Orderby
The field in our Compute Using is used in the Lookup function
The value in the box becomes a negative if we selected Previous or positive if we selected Next
The key addition to make this functionality possible is the LOOKUP function inside of Tableau Prep. This is a function that you may have made use of for Table calculations in Tableau Desktop. So to put this calculation in plain terms, we are telling Prep:
"For each Category, order the data by ascending Year of Sale and lookup the Sales value in the current row (indicated by the 0) and subtract the Sales value from the previous row (indicated by the -1)".
Being able to write these calculations brings a new level of flexibility and optimization to your data prep process. Difference from previous or even percent difference from previous are calculations that are often used for KPI cards in dashboards, but you usually have to create several versions of these calculations to get the correct Icon and Color indicators you are trying to incorporate. All of that processing can now be pushed into the Prep flow, freeing up compute on the workbook side and providing a smoother Cloud/Server experience.
Percent Difference From
You can navigate to the Percent Difference From calculation from the ellipses in the column, or you can change the calculation type directly in the Visual Editor
This changes the calculation as well as the visual explanation for what's happening
This time, row B is being subtracted from row A before and then it gets divided by row A. Now you have a percent change that is useful for those KPI cards.
The calculation written out is as follows:
(
{{PARTITION [Category]: ORDERBY [Year of Sale] ASC: LOOKUP([Sales], 0)}}
-
{{PARTITION [Category]: ORDERBY [Year of Sale] ASC: LOOKUP([Sales], -1)}}
)
/
{{PARTITION [Category]: ORDERBY [Year of Sale] ASC: LOOKUP([Sales], -1)}}Here we are telling Prep:
"For each Category, order the data by ascending Year of Sale and lookup the Sales value in the current row (indicated by the 0) and subtract the Sales value from the previous row (indicated by the -1). Divide the resulting value by the Sales value from the previous row".
Moving Sum or Average
We can also create a moving sum or average by selecting Moving Calculation. This time, the visual editor will have an option to select Sum or Average as the aggregation
The dotted line around the Compute Using value shows which rows are being aggregated. This is a useful capability to show rolling trends or reduce the effects of seasonality when looking at overall performance.
Creating a Running Sum
One functionality I was a little disappointed wasn't included was the ability to dynamically create a running sum. That doesn't mean we can't do it, it's just a bit of a manual process. Since the data set has 2015 - 2018, I created a calculation to include each year's Sales amount on each row. I started with 3 years ago sales since the last year, 2018, would be looking 3 years back at 2015
3 Years Ago Sales
{ PARTITION [Category]: { ORDERBY [Year of Sale] ASC: LOOKUP([Sales], -3)}}The lookup value of -3 is telling Prep to find the value from 3 rows back. Now I want to be able to add this to the rest of my fields, so I'm going to convert any Null values to 0
Then I will create the remaining calculations and also convert nulls to 0
2 Years Ago Sales
{ PARTITION [Category]: { ORDERBY [Year of Sale] ASC: LOOKUP([Sales], -2)}}Previous Year Sales
{ PARTITION [Category]: { ORDERBY [Year of Sale] ASC: LOOKUP([Sales], -1)}}I can add all these values together to give the effect of a running sum:
Running Sum of Sales
[3 Years Ago Sales] + [2 Years Ago Sales] + [Previous Year Sales] + [Sales]
Final Thoughts
Overall I really love this new feature and I am excited to see how it's used and how Tableau continues to make additions and improvements to it. The ability to strategically create KPI calculations baked in to the data set provides opportunities for huge performance improvements. BUT -
Be Careful not to Over Aggregate
Tableau's aggregation within the workbook is where most of the magic happens. You don't want to over aggregate your data set to where you lose some of that flexibility inside of the workbook. Sometimes it makes sense to leave that calculation in the workbook depending on how you want your users to interact with the data.