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.
Calculated fields provide the environment needed to create this view. Refer to the How to Create a Calculated Field topic in the Online Help for more information.
For more information, refer to the Writing formulas in Tableau topic in the Online Help.
Select Percentage, and click OK.
In the Measures pane, right-click on 2011 and select Field Properties > Number Format > Currency (Custom).
Repeat steps 4 and 5 for the 2012 measure.
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.
Click OK.
On the Measures pane, double-click Measure Values field.
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. Refer to the How to Create a Calculated Field topic in the Online Help for more information.
Step 1
Open Tableau Desktop and connect to the Sample - Superstore 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.
For more information, refer to the Writing formulas in Tableau topic in the Online Help.
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.