KNOWLEDGE BASE

Creating a Rolling Calculation


Product(s): Tableau Desktop
Version(s): 8.3, 8.2, 8.1, 8.0
Last Modified Date: 16 Aug 2016

Article Note: This article is no longer actively maintained by Tableau. We continue to make it available because the information is still valuable, but some steps may vary due to product changes.


Rolling calculations, specifically moving averages, are often useful for pulling in one-off outliers and smoothing out short-term fluctuations. Moving averages are often performed on time series data. In retail sales, this calculation is useful for flattening seasonal sales trends to see long-term trends better.

This example walks you through creating worksheets to show weekly sales and weekly sales averages, comparing them side by side in a dashboard, and comparing them in an overlay.

Set up a worksheet to show a year’s weekly averages

Step 1

Open a new workbook and connect to the Superstore sample.

Step 2

From the Dimensions pane, drag Order Date to the Columns shelf, and then drag a second instance to the Filters shelf.

Step 3

In the Filter Field dialog box, select Years and then click Next.

Step 4

In the Filter dialog box, clear the check boxes for all years except 2012, and then click OK.

Step 5

On the Columns shelf, on the Year (Order Date) drop-down menu, select More > Custom.

Step 6

In the Custom Date dialog box, in the Detail list, select Week numbers, then select Date Part, and then click OK.

Step 7

From the Measures pane, drag Sales to the Rows shelf.

Step 8

On the Rows shelf, right-click Sales, and then select Add Table Calculation.

Step 9

In the Table Calculation dialog box, complete the following steps:

  1. In the Calculation Type list, select Moving Calculation.
  2. In the Summarize values using list, select Average.
  3. To average sales over the previous three weeks, leave Previous Values set to 2, leave the Next Values set to 0, and keep the Include current value check box selected.
  4. Click OK.

Step 10

Right-click the worksheet tab, select Rename Sheet, and name it 2012 Weekly Sales week #.

Create a worksheet to show dates instead of week numbers

You can use a calculated field to group all of the dates in a specified period.

Step 1

  • For Tableau Desktop 7.0 and 8.0, right-click the worksheet tab, and select Duplicate Sheet.
  • For Tableau Desktop 6.1 and earlier, select Edit > Duplicate Sheet.

Step 2

On the new worksheet, select Analysis > Create Calculated Field.

Step 3

In the Calculated Field dialog box, complete the following steps.

  1. For Name, type Week_trunc.
  2. In the Formula box, build the following formula:

    DATETRUNC('week', [Order Date] )

  3. Confirm that the status message indicates that the formula is valid, and then click OK.

Step 4

From the Dimensions pane, drag Week_trunc to the Columns shelf.

Step 5

  • Tableau Desktop 7.0 and 8.0: On the Columns shelf, right-click YEAR(Week_trunc), and select Exact Date.
  • Tableau Desktop 6.1 and earlier: On the Columns shelf, right-click YEAR(Week_trunc) and select All Values.

Step 6

On the Columns shelf, right-click WEEK(Order Date) and select Remove.

Step 7

Right-click the worksheet tab, select Rename Sheet, and name the worksheet 2012 Weekly Sales.

Compare regular sales to the moving average

To compare the regular Sales to the moving average, you create a sheet for each.

Step 1

Create and rename a new worksheet.

  • Tableau Desktop 7.0 and 8.0: Right-click the 2012 Weekly Sales worksheet tab, and then select Duplicate Sheet.
  • Tableau Desktop 6.1 and earlier: Select the 2012 Weekly Sales worksheet, and then select Edit > Duplicate Sheet.

Step 2

Right-click the worksheet tab, select Rename Sheet, and name the new sheet 2012 Weekly Sales Moving Avg.

Step 3

Display the 2012 Weekly Sales worksheet, and on the Rows shelf, right-click SUM(Sales) and select Clear Table Calculation.

Now you set the y-axis on the two worksheets to the same range.

Step 4

Right-click the y-axis, and then select Edit Axis.

Step 5

In the Edit Axis dialog box, make the following changes:

  1. For Range, select Fixed.
  2. Set the Start and End values to 0 and 400,000.

Step 5

Display the 2012 Weekly Sales Moving Avg worksheet and make the same changes for the y-axis.

Create a dashboard

Complete these steps to create a dashboard that shows both worksheets side-by-side to compare.

Step 1

  • For Tableau Desktop 7.0 and 8.0, select Dashboard > New Dashboard.
  • For Tableau Desktop 6.1 and earlier, select Edit > New Dashboard.

Step 2

Drag 2012 Weekly Sales to the dashboard.

Step 3

Drag 2012 Weekly Sales Moving Avg to the dashboard and position it to the left of 2012 Weekly Sales.

Create an overlay

An overlay is another way of comparing the sales and the moving average.

Step 1

  • Tableau Desktop 7.0 and 8.0: Right-click the 2012 Weekly Sales Moving Avg worksheet tab and select Duplicate Sheet.
  • Tableau Desktop 6.1 and earlier: Select the 2012 Weekly Sales Moving Avg worksheet and select Edit > Duplicate Sheet.

Step 2

On the new sheet, from the Measures pane, drag Measure Values to the Rows shelf.

Step 3

From the Dimensions pane, drag Measure Names to the Filters shelf.

Step 4

In the Filter dialog box, clear all the check boxes except for Sales, and then click OK.

Step 5

From the Dimensions pane, drag Measure Names to Color on the Marks card.

Drag another instance of Measure Names from the Dimensions pane to Size.

Color and size make the lines easier to differentiate visually.

Tip: In Tableau 8.0, to adjust the mark size, you can also click the Marks card that represents a specific set of marks (instead of All), and adjust the Size slider. Then do the same for the other set if you want to differentiate them even further.

Step 6

From the Rows shelf, drag SUM(Sales) to the Measure Values shelf.

 

Alternate Search Terms:Tableau Digital Calculations Filters
Did this article resolve the issue?