[TABLEAU] Creating A Rolling Calculation

Creating a Rolling Calculation

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 Connect to the Superstore sample. Step 2 From the Dimensions pane, drag Order Date to the Columns shelf and 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 2009, and then click OK. Step 5 On the Columns shelf, right-click Year (Order Date), and select More > Week Number. Step 6 From the Measures pane, drag Sales to the Rows shelf. Step 7 On the Rows shelf, right-click Sales and select Add Table Calculation. Step 8 In the Table Calculation dialog box, in the Calculation Type list, select Moving Calculation. Step 9 In the Summarize values using list, select Average. Step 10 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. Step 11 Click OK. Step 12 Right-click the worksheet tab and select Rename Sheet. Step 13 Replace the highlighted Sheet 1 with the name 2009 Wkly Sales with Week #. Create a worksheet to show dates instead of week numbers You can use a calculated field to group all the dates in a specified period. Step 1 For Tableau Desktop 7.0 and later, 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, make the following selections to create this formula: DATETRUNC('week', [Order Date] ) In the Name text box, give the calculation the name _week_trunc. The underscore ensures that the field appears at the top of the Dimensions pane. Press the Tab key to move the cursor to the Formula text box. In the Functions list, double-click DATETRUNC. Type 'week', (week between single quotes, following by a comma and a space). In the Fields list, double-click Order Date. Step 4 When finished, click OK. Step 5 From the Dimensions pane, drag your calculated field (_week_trunc) to the Columns shelf. Step 6 For Tableau Desktop 7.0 and later, on the Columns shelf, right-click _week_trunc, and select Exact Date. For Tableau Desktop 6.1 and earlier, on the Columns shelf, right-click _week_trunc and select All Values. Step 7 Right-click WEEK(Order Date) and select Remove. Step 8 Right-click the worksheet tab and select Rename Sheet. Step 9 Replace the highlighted Sheet 2 with the name 2009 Wkly 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 For Tableau Desktop 7.0 and later, right-click the 2009 Wkly Sales worksheet tab, and then select Duplicate Sheet. For Tableau Desktop 6.1 and earlier, select the second sheet, 2009 Wkly Sales, and then select Edit > Duplicate Sheet. Step 2 Right-click the worksheet tab of the new worksheet and select Rename Sheet. Step 3 Replace the highlighted Sheet 3 with the name 2009 Wkly Sales Mvg Avg. Step 4 On the 2009 Wkly Sales sheet (the original that you just copied), on the Rows shelf, right-click SUM(Sales) and select Clear Table Calculation. Now you need to set the Y axis on the two worksheets to the same range. Step 5 On one of the worksheets, right-click the Y axis and select Edit Axis. Step 6 In the Edit Axis dialog box, under Range, select Fixed. Step 7 In the Start text box, keep 0, and in the End text box, type 200,000. Step 8 When finished, click OK. Step 9 Repeat steps 5 through 8 on the other worksheet. Create a dashboard You can create a dashboard that shows both sheets side by side to compare. Step 1 For Tableau Desktop 7.0 and later, select Dashboard > New Dashboard. For Tableau Desktop 6.1 and earlier, select Edit > New Dashboard. Step 2 Drag 2009 Wkly Sales and 2009 Wkly Sales Mvg Avg onto the dashboard sheet and place them side by side. Create an overlay You can create an overlay as another way of comparing the sales and the moving average. Step 1 For Tableau Desktop 7.0 and later, right-click the 2009 Wkly Sales Mvg Avg worksheet tab, and then select Duplicate Sheet. For Tableau Desktop 6.1 and earlier, select the 2009 Wkly Sales Mvg Avg worksheet, and then select Edit > Duplicate Sheet. Step 2 On the duplicate 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 the Color shelf and the Size shelf on the Marks card. Color and size make the two lines easier to differentiate visually. Step 6 From the Rows shelf, drag SUM(Sales) to the Measure Values shelf.