[TABLEAU] Comparing Table Calculations

Comparing Table Calculations

This example compares the difference in total sales to the difference in average sales from year to year. It does this by creating: a table calculation that determines the difference in total sales from year to year a table calculation that determines the difference in the moving average of sales from year to year a calculated field that subtracts the difference in total sales from the difference in the moving average of sales from year to year To create a calculated field based on a table calculation, you click Customize in the Add Table Calculation dialog box. In the Calculated Field dialog box, you can refine the table calculation further as necessary. You can also edit table calculations directly, in the Edit Table Calculation dialog box. Set up the preliminary view Step 1 Open a new workbook and connect to the Superstore sample. Step 2 In the Dimensions pane, double-click Order Date and Product 1 - Category. Order Date appears on the Columns shelf, and Product 1 - Category appears on the Rows shelf. Step 3 In the Measures pane, double-click Sales. Sales appears on the Text shelf on the Marks card. Create calculated fields from table calculations First, you create a table calculation and a calculated field for the difference in sales. Step 1 On the Text shelf, right-click Sales and select Add Table Calculation. The default settings are: Calculation Type: Difference From Calculate the difference along: Table (Across) Display the value as a difference from: Previous For this example, you do not have to change these default values. Step 2 In the Table Calculation dialog box, click Customize. Step 3 In the Calculated Field dialog box, in the Name text box, type Difference in Sales (Custom). The suggested calculation is correct for your purposes. Step 4 Click OK. Step 5 In the Table Calculation dialog box, click OK. Now you create the table calculation and calculated field for the moving average of sales from year to year. Step 6 In the Measures pane, double-click Sales again. Step 7 On the Measure Values 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 Select the check box for Perform a secondary calculation on the result. Step 11 Click Customize. Step 12 In the Calculated Field dialog box, in the Name text box, type Difference in Moving Average of Sales (Custom). The suggested calculation is correct for your purposes.   Step 13 Click OK. Step 14 In the Table Calculation dialog box, click OK. Create a calculated field that compares table calculations Now you can compare the difference in sales from year to year to the moving average of sales from year to year. Step 1 Select Analysis > Create Calculated Field. Step 2 In the Calculated Field dialog box, make the following selections to create this formula: In the Name text box, type Difference between Moving Average and Current Sales. Click the Formula text box. In the Fields list, double-click Difference in Moving Average of Sales (Custom). Type a minus sign. In the Fields list, double-click Difference in Sales (Custom). Step 3 When finished, click OK. Step 4 In the Measures pane, double-click Difference between Moving Average and Current Sales along Table (across). Note : Some calculated fields may not appear correctly as the table calculations require different dimensions to calculate within. Edit table calculations If you want to change the way a table calculation is set up, you can do so at any time. On the Measure Values shelf, right-click the calculation you want to change, and select Compute Using. The list offered in the submenu is the same list that is offered in the Table Calculation dialog box