[TABLEAU] Adding A Calculated Column To A View

Adding a Calculated Column to a View

Sometimes columns in your text table do not allow you to display the results of certain calculations as you might expect. In cases like this, you can create a calculated column that uses a single formula that automatically adjusts the value for each row in the table. For example, suppose you want to create a view that displays the sales for each year in several columns and the year-over-year (YOY) percentage change in the final column. Because determining the YOY percentage change requires a calculation, you can create the view in one of two ways. You can create custom calculations yourself and add them to the view, or use a combination of a quick table calculation and a dashboard. Option 1: Create calculated fields Using option 1 and the example below, you can create three calculations to obtain the YOY percent change. Then, you can use a text table to display a column for sales in 2011, a column for sales in 2012, and a column that displays the YOY percent change in sales between 2011 and 2012.  Calculated fields provide the environment needed to create this view.  Step 1 Open Tableau Desktop and connect to the Sample - Superstore Sales (Excel) data source.  Step 2 Select Analysis > Create Calculated Field.  Step 3 In the Calculated Field dialog box, do the following tasks: In the Name text box, type 2011. In the Formula text box, type the following: IF YEAR([Order Date]) = 2011 THEN [Sales] ELSE 0 END Click OK. Step 4 Select Analysis > Create Calculated Field to create a second calculated field. Step 5 In the Calculated Field dialog box, do the following tasks: In the Name text box, type 2012. In the Formula text box, type the following: IF YEAR([Order Date]) = 2012 THEN [Sales] ELSE 0 END Click OK.   Step 6 Select Analysis > Create Calculated Field to create a third calculated field. Step 7 In the Calculated Field dialog box, do the following tasks: In the Name text box, type YOY % Change. In the Formula text box, type the following: (SUM([2012]) - SUM([2011])) / SUM([2011]) Click OK.   Set up the view and format the numbers Step 1 In the Measures pane, double-click the 2011, 2012, and YOY % Change fields, in that order.  Step 2 In the Measures pane, right-click on YOY % Change and select Field Properties > Number Format.   Step 3 Select Percentage, and click OK.. Step 4 In the Measures pane, right-click on 2011 and select Field Properties > Number Format > Currency (Custom). Step 5 Set the Decimal places to 0, and click OK.  Step 6 Repeat steps 4 and 5 for the 2012 measure. Option 2: Use a quick table calculation and dashboard Using option 2 and the example below, you can determine the YOY percent change using a table calculation instead. Then, to display the years columns next to the YOY column, you must first create one worksheet that displays the year information, and then a second worksheet that displays the YOY information. Finally, you combine both worksheets on a dashboard. In the following example, a dashboard displays text tables from two worksheets. The first text table shows sales for 2011 and 2012. The second table shows the YOY percent change in  sales between 2011 and 2012. Step 1 Open Tableau Desktop and connect to Sample - Superstore Sales (Excel) data source.  Step 2 From the Measures pane, drag the Sales field to the Rows shelf.  Step 3 From the Dimensions pane, drag the Order Date field to the Columns shelf.  Step 4 From the Dimensions pane, drag the Order Date field to the Filters shelf.  Step 5 In the Filter Field dialog box, select Years, click Next, and then select the 2011 and 2012 check boxes. Step 6 Click OK.  Step 7 On the Show Me shelf, select the text tables chart option. Create a second text table Step 1 Right-click on the Sheet 1 tab and select Duplicate Sheet.  Step 2 In the duplicated sheet, on the Text shelf, right-click SUM(Sales) field and select Quick Table Calculation > Percent Difference. Step 3 On the Measures pane, double-click  Measure Values field.  Step 4 In the view, right-click the % Difference in Sales from  header and remove the selection next to Show Header.  Step 5 On the view, right-click the 2011 header and select Hide. Create the dashboard Step 1 Select Dashboard > New Dashboard to create a new Dashboard.  Step 2 From the Dashboard window, click and drag Sheet 1 and Sheet 2 to the dashboard.  Step 3 On the dashboard, adjust column widths so that the tables align with each other.  Step 4 Right-click the title area on Sheet 1 select Hide Title. Repeat this step for the Sheet 2 title.  Alternate Search Terms: yoy, difference, calculation in column, calc, calculation