Advanced Analysis
Actions
Filter Actions
Highlight Actions
Selecting Marks to Highlight
Color Legend Highlighting
Highlight Toolbar Button
Creating Advanced Highlight Actions
URL Actions
Running Actions
Actions and Dashboards
Example: Filter Actions in a Dashboard
Example: URL Actions in a Dashboard
Using Field and Filter Values in Actions
Using Field and Filter Values in URLs
Using Field and Filter Values in Action Names
Calculations
Aggregations
How Aggregation and Disaggregation Work
Aggregating Data
Disaggregating Data
Example – Aggregating and Disaggregating Data
Calculated Fields
How to Create a Calculated Field
Copying and Pasting Calculated Fields
Writing formulas in Tableau
Example – Creating a Calculated Field
Aggregate Calculations
About Aggregate Calculations
How to Create an Aggregate Calculation
Aggregate Calculations in a Disaggregated State
Example – Aggregate Calculation
Example – Spotlighting Using Calculations
Table Calculations
Understanding Table Calculations
Addressing and Partitioning
Quick Table Calculations
Defining Basic Table Calculations
Difference From Calculation
Percent Difference From Calculation
Percent From Calculation
Percent of Total Calculation
Running Total Calculation
Moving Calculation
Secondary Table Calculations
Customizing Table Calculations
Binned Data
Example – Creating a Histogram with Binned Data
Totals
Grand Totals
How to Turn on Grand Totals
Grand Totals and Aggregations
Example – Grand Totals and Aggregations
Subtotals
Percentages
About Percentages
Percentages and Aggregations
Example – Percentages and Aggregations
Percentage Options
Percent of Table
Percent of Column
Percent of Row
Percent of Pane
Percent of Row in Pane
Percent of Column in Pane
Parameters
Creating Parameters
Editing Parameters
Using Parameters in Calculations
Parameter Controls
Example – Parameters
Background Images
Adding Background Images
Setting up the View
Managing Background Images
Editing an Image
Enabling/Disabling Images
Adding Show/Hide Conditions
Removing an Image
Background Maps
Geographic Roles
Building a Map View
Map Options
Map Layers
Data Layers
Washout
Setting a Default Location
Editing Locations
Custom Geocoding
Creating an Import File
Extending an Existing Role
Adding New Roles
Adding New Hierarchies
Importing Custom Geocoding
Saving Custom Geocoding
Background Map Sources
Working with WMS Servers
Setting a Default Map Source
Map Storing and Working Offline
Trend Lines and Statistics
Adding Trend Lines
Add Trend Lines to the View
Why can’t I add Trend Lines?
Remove Trend Lines
The Trend Line Model
Removing Factors from the Model
Testing Significance
Entire Model Significance
Significance of Specific Fields
Significance of Individual Trend Lines
Trend Lines Example
Assumptions
Trend Line Model Terms
Model Formula
Number of Observations
Residual DF (residual degrees of freedom)
DF (degrees of freedom)
SSE (sum squared error)
MSE (mean squared error)
R-Squared
Standard error
P (significance)
Analysis of Variance
Individual trend lines
Commonly Asked Questions
Log Axes
Advanced Analysis
Now that you understand the basics of building views in Tableau, become an advanced user by learning how to create custom calculations, use the built in statistics tools, leverage dynamic paramters, map data, and more.
• Actions
• Calculations
• Parameters
• Background Images
• Background Maps
• Trend Lines and Statistics
• Log Axes
Actions
Tableau allows you to add context and interactivity to your data using actions. Link to web pages, files, and other Tableau worksheets directly from your analytical results. Use the data in one view to filter data in another as you create guided analytical stories. Finally, call attention to specific results using highlighting.
For example, in a dashboard showing home sales by neighborhood you could use actions to help you quickly see relevant information for a selected neighborhood. Select a neighborhood in one view which then highlights the related houses in a map view, filters a list of the houses sold, and opens a webpage showing census data for the neighborhood.
There are three kinds of actions in Tableau: Filter, Highlight, and URL actions. This section discusses the following topics:
• Filter Actions
• Highlight Actions
• URL Actions
• Running Actions
• Actions and Dashboards
• Using Field and Filter Values in Actions
Filter Actions
Filter actions are a way to send information between worksheets. Typically a filter action is used to send information from a selected mark to another sheet showing related information. For example, when looking at a view showing the sales price of houses, you may want to be able to select a particular house and show all comparable houses in a different view. You could define a filter actions to accomplish this task. First you need to decide what comparable means. In this case, say that comparable houses are houses with a similar sale price and square footage. A filter action to show comparable houses can be defined by selecting a destination worksheet and defining filters on sales price and square footage.
Filter actions work by sending the data values of the relevant source fields as filters to the destination sheet. If you launch the filter action described in this example from a house that sold for $450,000, the destination sheet will have a filter to only show houses that sold for the same amount.
To create a filter action:
1. Select Edit > Actions.
2. In the Actions dialog box, click Add Action and then select Filter.
3. In the subsequent dialog box specify a name for the Action.
Use a name that defines the action. If you choose to run the action using the menu the name is the option that shows on the menu. For example, when sending housing information from one sheet to a map, the name could be “Map all comparable houses sold in February” You can use variables in the name that will be filled in based on the values of the selected field.
4. Use the drop-down list to select a source sheet or data source. When you select a data source or dashboard sheet you can further refine by selecting the individual sheets you want to launch the action from.
5. Then select how you want to launch the action. Select one of the following options:
o Hover – rest the pointer over a mark in the view to run the action. This option works well for highlight and filter actions within a dashboard.
o Select – click on a mark in the view to run the action. This option works well for all types of actions.
o Menu – right-click a selected mark in the view and then select an option on a the context menu. This option works well for filter and URL actions.
6. Use the second drop-down list to select a target sheet. When you select a dashboard sheet you can further refine the target by selecting one or more sheets within the dashboard.
7. Specify what do do when the select is cleared in the view. You can select from the following options:
o Leave the filter – leaves the filter on the target sheets. The target views in the dashboard will show the filtered results.
o Show all values – changes the filter to include all values.
o Exclude all values – changes the filter to exclude all values. This option is useful when you are building dashboards that only show some sheets if a value in another sheet is selected.
8. Setup one or more filters to specify the data that you want to show on the target sheets. You can filter on All Fields or define filters on Selected Fields.
9. If you are defining filters for specific fields click Add Filter.
10. In the Add Filter dialog box select a source and target data sources and fields. When you run the action from a specific mark on the source sheet, a filter is added to the target sheet that only includes values for the target field that match the values of the source field. In the comparable houses sheet link example, the Source Field is Beds and the Target Field is Beds. That means when you launch the sheet link for a house that has 3 bedrooms, the destination worksheet will only show houses that also have 3 bedrooms.
11. When finished, click OK three times to close the dialog boxes and return to the view.
You can add sheet links across data sources even if the field names are not exactly the same. One data source may have a field titled Latitude while another has a Lat field. Using the drop down lists in this dialog box, you can associate the Latitude field to the Lat field.
Note:
The fields available in the Target Field drop-down list are dependent on what you selected as the Source Field. Only fields with the same data type as the source field can be selected as a destination field.
Highlight Actions
Highlight actions allow you to call attention to marks of interest by coloring select marks and dimming all others. You can highlight marks in the view by selecting the marks you want to highlight, use the color legend to select related marks, or create an advanced highlight action.
• Selecting Marks to Highlight
• Color Legend Highlighting
• Highlight Toolbar Button
• Creating Advanced Highlight Actions
• Selecting Marks to Highlight
• When you select a mark in the view all other marks are dimmed to draw attention to the selection. Selection is saved with the workbook and can be included when publishing. The simplest way to add highlighting to a view is to select the marks you want to highlight.
• You can select multiple marks by holding down the Ctrl key on your keyboard while you select each mark. You can also click and drag the pointer to select all marks in a specific area of the view.
•
•
Color Legend Highlighting
Color legend highlighting is a powerful analytical mode for the color legend that allows you to focus on select members in the view. When you turn on color legend highlighting the marks associated with the selected items in the color legend are colored while all other marks are gray.
For example, the views below show the relationship between order quantity and profit for several products. The view on the left uses the normal color legend, all marks are colored based on their shipping mode. The view on the right uses legend highlighting to call out the products that were delivered via Delivery Truck.
You can easily switch between legend highlighting and normal modes using the color legend card menu. Then, if you like how a view is highlighted, you can assign the highlight colors to the color palette. The old colors are replaced with the highlight colors.
To turn on color legend highlighting:
1. Click the Highlight button at the top of the color legend or select Highlight Selected Items on the color legend card menu.
2. Select an item in the color legend.
Once legend highlighting is turned on, you can quickly focus on specific data in the view by selecting different items in the color legend. When color legend highlighting is turned on a Highlight Action is created and can be modified in the Actions dialog box.
To turn off color legend highlighting:
• Click the Highlight button at the top of the color legend or select Highlight Selected Items on the color legend card menu.
When you turn color legend highlighting off the action is removed from the Actions dialog box.
If you like how the view is highlighted and want to keep a specific member highlighted even when you turn off legend highlight mode, you can assign the highlight colors to the existing color palette. The original color legend is discarded and the highlight colors become the new color palette for the legend.
To assign the highlight colors to the color palette:
• Select Assign Highlight Colors to Palette on the color legend card menu.
Highlight Toolbar Button
Another way to add a highlight action is using the highlight button in the toolbar.Similar to the color legend highlighting, the toolbar button lets you highlight a collection of related marks in the view. To turn on highlighting, select the fields you want to use for highlighting on the toolbar menu. Then select a mark in the view to see the related data.
For example, the view below shows sales vs. profit by region. When a mark is selected, all other marks from that region that were shipped using the selected ship mode are highlighted. In this case you can quickly see all products from the Wester region that were shipped via Delivery truck.
The toolbar menu also lets you highlight on All fields or Dates & Times. All fields will consider all fields when determining matching records. Dates & Times considers all date and time fields.
When you use the Highlight toolbar button an action is created in the Actions dialog box. You can modify the action to create more advanced highlighting behavior.
Finally, you can use the toolbar button to disable highlighting across the entire workbook or for just the active sheet.
Creating Advanced Highlight Actions
You can define more advanced highlight actions using the Actions dialog. There you can specify source and target sheets along and the fields you want to use for highlighting. Follow the steps below to create a Highlight Action.
To create a highlight action:
1. Select Edit > Actions.
2. In the Actions dialog box click the Add Action button and then select Highlight.
3. Give the action a name that will identify it in the Actions dialog. Try to make it descriptive. For example, Highlight Products Shipped by Delivery Truck. You can use variables in the name that will be filled in based on the values of the selected field.
4. Use the drop-down list to select the Source sheet or data source. If you select a data source or a dashboard sheet you can further select individual sheets within them.
5. Select how you want to launch the action. You can select from the following options:
o Hover – rest the pointer over a mark in the view to run the action. This option works well for highlight and filter actions within a dashboard.
o Select – click on a mark in the view to run the action. This option works well for all types of actions.
o Menu – right-click a selected mark in the view and then select an option on a the context menu. This option works well for filter and URL actions.
6. Select a Target sheet. If you select a dashboard you can further select individual sheets within the dashboard.
7. Select the fields you want to use for highlighting. Select from the following options:
o Selected Fields – marks in the target sheet are highlighted based on select fields. For example, highlighting using the Ship Mode field will result in an action that highlights all marks in the target sheet that have the same ship mode as the selected mark in the source sheet.
o Dates and Times – marks in the target sheet are highlighted when their date and time match those of the marks selected in the source sheet. All dates and time fields are considered when determining a match.
o All Fields – marks in the target sheet are highlighted when they match the marks selected in the source sheet. All fields are considered when determining a match.
8. When finished, click OK twice to close the dialog boxes and return to the view.
URL Actions
A URL action is a hyperlink that points to a Web page, file, or other web-based resource outside of Tableau. You can use URL actions to link to more information about your data that may be hosted outside of your data source. To make the link relevant to your data, you can substitute field values of a selection into the URL as parameters.
To add a Hyperlink:
1. Select Edit > Actions.
2. In the Actions dialog box, click Add Action and then select URL.
3. In the subsequent dialog box, specify a name for the link. Make the name descriptive of the action. If you choose to run the action using the menu the name is the option that shows on the menu. For example, when linking to more product details, the name could be “Show More Details for Binder Clips.” You can use variables in the name that will be filled in based on the values of the selected field.
4. Use the drop-down list to select a source sheet or data source. If you select a data source or dashboard you can select individual sheets within it.
5. Select the fields you want to use for highlighting. Select from the following options:
o Hover – rest the pointer over a mark in the view to run the action. This option works well for highlight and filter actions within a dashboard.
o Select – click on a mark in the view to run the action. This option works well for all types of actions.
o Menu – right-click a selected mark in the view and then select an option on a the context menu. This option works well for filter and URL actions.
6. Specify the URL. You can use any URL that your browser can recognize including web pages, ftp resources, and files.
Just as you can use variables in the name of the URL, you can also use field values and filter values as parameters in the URL. That means that you can send information about each selected mark or filter setting to a given website.
7. Optionally select one or more of the following options:
o URL Encode Data Values – select this option if your data contains values that use characters that are not allowable in a URL. For example if one of your data values contains an ampersand, such as “Sales & Finance,” the ampersand must be translated into characters that your browser understands (URL encoded) if you want to include that value in the URL.
o Enable Multi-Select – select this option if you are linking to a webpage that can take lists of values as parameters in the link. For example, say you select several products in a view and you want to see each product’s details hosted on a webpage. If the server can load multiple product details based on a list of identifiers (product ID or product name), you could use multi-select to send the list of identifiers as parameters.
When you enable multi-select you must also define the item delimiter, which is the character that separates each item in the list (often a comma). You must also define the Delimiter Escape, which is used if the delimiter character is used in a data value.
8. When finished, click OK twice to close the dialog boxes and return to the view.
Note:
URL actions can also point to a web page object in a dashboard. Refer to Actions and Dashboards to learn more about how actions work with dashboards.
Running Actions
Depending on how the action is created you can run an action using one of the following three methods:
• Hover – rest the pointer over a mark in the view to run the action. This option works well for highlight and filter actions within a dashboard.
• Select – click on a mark in the view to run the action. This option works well for all types of actions.
• Menu – right-click a selected mark in the view and then select an option on a the context menu. This option works well for filter and URL actions.
Hover Select Menu
Links are not always visible for every worksheet and mark. Because links are mapped to specific fields in the data source, links will only be available for the worksheets that use the mapped fields. For example, if you add a hyperlink that uses both Latitude and Longitude as parameters in the link, the link will only be available to worksheets that use Latitude and Longitude in the view. Additionally, the link is only available on marks and headers that contain relevant values.
Actions and Dashboards
Actions often have special behavior when the source or destination is a dashboard. Filter and Highlight actions can affect other views in the dashboard and and URL actions can update a webpage object so you don’t have to open your web browser. Finally, you can create simple Filter and Highlight actions using special menu options so you don’t have to open the Actions dialog box.
• Example: Filter Actions in a Dashboard
• Example: URL Actions in a Dashboard
• Example: Filter Actions in a Dashboard
• This example shows how to create a filter action in a dashboard. The example shows a Real Estate dashboard with three views. Using the Use as Filter option you can set one of the views to act as a filter on all the other views in the dashboard. In this case the scatter plot in the upper right is filtering the map view and the text table to show more details about the selected houses.
•
•
•
• The Use as Filter command can only apply to one view at a time. A filter action is created that you can modify in the Actions dialog box.
Example: URL Actions in a Dashboard
This example shows how a URL action works with a web page object in a dashboard. Below is a dashboard showing sales information by product for several stores in a coffee franchise. Included in the dashboard is a web page object that shows product details. The text table has a URL action that points at that web page. When you launch the action the web page automatically updates within the dashboard rather than opening a web browser.
Using Field and Filter Values in Actions
When you add an action in Tableau you often want to use values from your data as parameters in the name of the action as well as the action itself. Using fields as variables in the action name makes the menu item that launches the action specific to the selected mark. More commonly, using field and filter values as parameters in the URL of a URL action allows you to send information about a specific data point or filter setting to the destination webpage.
• Using Field and Filter Values in URLs
• Using Field and Filter Values in Action Names
Using Field and Filter Values in Action Names
In addition to using field and filter values in URLs, you can use field and filter information as variables in the action names. The name of the action displays on the context menu when an action is launched using the menu. Using field and filter variables in the name is useful in making the action specific to the selected mark. In a view showing real estate information, you could name a URL action that points at satellite images from an online mapping service, “Show satellite image of <Address>.” When you right-click on a specific mark, the <Address> tag is replaced with the location value associated with that mark.
To add a field or filter as a variable in a Name:
1. In the Add Action dialog box, begin typing the name for the action.
2. Place the cursor where you want to insert the field or filter value.
3. Click the arrow to the right of the text box and select the field or filter you want to add as a variable. The field or filter name is added between angle brackets.
Calculations
To extract meaningful results from your data, you might want to perform one or more calculations. Some calculations are predefined in Tableau, while you can customize others to suit your specific needs. The following calculations are supported:
• Ag gre gations – View your data at different levels of detail. For example, you might want to view data in an aggregated state such as a summation or an average, or you might want to view the data in a disaggregated state and work with the individual rows of a data source.
• Calcula ted Fields– Create new fields that are based on existing data source fields, and common functions and operators. Use a standard dialog box that shows available functions and fields to author these custom fields.
• Table Calculations – Create calculations that are applied to the values in the entire table and are often dependent on the table structure itself, such as running totals and year to date growth.
• Bin ned Data – Create new fields that are based on binned measures.
• Su btot als – Add subtotals to the rows and columns of a table.
• Gr and Totals – Add totals to the rows and columns of a table.
• Pe rcent ages – View data as percentages rather than as absolute numbers. The percentages can be based on rows, columns, panes, or the entire table.
You can use all of these different types of calculations simultaneously. For example, you can create a new calculated field called Profit that is the difference between the Sales and Cost fields. You could then apply an aggregation (like a summation) to this new field in order to view total profit over time. You could then display the numbers as percentages and turn on grand totals to see how these percentages vary from category to category. Finally, you could bin the new field and display the data as a histogram.
• Aggregations
• Calculated Fields
• Table Calculations
• Binned Data
• Totals
• Percentages
Aggregations
Sometimes it is useful to look at numerical data in an aggregated form such as a summation or an average. The mathematical functions that produce aggregated data are called aggregation functions. Aggregation functions perform a calculation on a set of values and result in a single value. For example, a measure that contains the values 1, 2, 3, 3, 4 aggregated as a sum results in a single value: 13.
For example, if you have 3,000 sales transactions from 50 products in your data source, you might want to view the sum of sales for each product, so that you can decide which products are the most important.
Tableau provides a set of predefined aggregations that are shown in the table below.
Aggregation Description Result for measure that contains 1, 2, 2, 3
ATTR Returns the value of the given expression if it only has a single value for all rows in the group, otherwise it displays an asterisk (*) character. Null values are ignored. N/A
Dimension Returns all unique values in a measure or dimension. 3 values (1, 2, 3)
Sum Computes the sum of the numbers in a measure. Null values are ignored. 1 value (8)
Average Computes the arithmetic mean of the numbers in a measure. Null values are ignored. 1 value (2)
Minimum Computes the smallest number in a measure or continuous dimension. Null values are ignored. 1 value (1)
Maximum Computes the largest number in a measure or a continuous dimension. Null values are ignored. 1 value (3)
Standard Deviation Computes the standard deviation of all values in the given expression based on a sample population. Null values are ignored. Returns a Null if there are fewer than 2 members in the sample that are not Null. Use this function if your data represents a sample of the population. 1 value (0.8165)
Standard Deviation Population Computes the standard deviation of all values in the given expression based on a biased population. Assumes that its arguments consist of the entire population. Use this function for large sample sizes. 1 value (0.7071)
Variance Computes the variance of all values in the given expression based on a sample. Null values are ignored. Returns a Null if there are fewer than 2 members in the sample that are not Null. Use this function if your data represents a sample of the population. 1 value (0.6667)
Variance Population Computes the variance of all values in the given expression based on a biased population. Assumes that its arguments consist of the entire population. Use this function for large sample sizes. 1 value (0.5000)
Count Counts the number of rows in a measure or a dimension. When applied to a dimension, Tableau creates a new temporary column that is a measure because the result of a COUNT is a number. You can count numbers, dates, booleans, and strings. Null values are ignored in all cases. 1 value (4)
Count Distinct Counts the number of unique values in a measure or dimension. When applied to a dimension, Tableau creates a new temporary column that is a measure because the result of a COUNT is a number. You can count numbers, dates, booleans and strings. Null values are ignored in all cases. This function is not supported for Microsoft Access, Microsoft Excel, and Text file data sources. 1 value (3)
Disaggregate Returns all records in the underlying data source. 4 values (1, 2, 2, 3)
You can also define custom aggregations as described in Aggre gate Calculations. Note that depending on the type of data view you create, Tableau will apply these aggregations at the appropriate level of detail. For example, Tableau will apply the aggregation to individual dimension members (the average delivery time in the East region), all members in a given dimension (the average delivery time in the East, West, and Central regions), or groups of dimensions (the sum of sales for all regions and for all markets).
You may specify a default aggregation for any measure that is not a user-defined aggregation. A default aggregation is a preferred calculation for summarizing a continuous or discrete field. The default aggregation is automatically used when a measure is first placed on a shelf. Change the default aggregation by right-clicking a measure in the Data window and selecting Field Properties > Aggregation. Below the default aggregation for the Budget Margin measure is set to Average.
Tableau also allows you to view data in disaggregated form (relational databases only). This is an extremely powerful feature. When data are disaggregated, you can view all of the individual rows of your data source. For example, after discovering that the sum of sales for rubber bands is $14,600, you might want to see the distribution of individual sales transactions. To answer this question, you need to create a view that shows individual rows of data. That is, you need to disaggregate the data (refer to How Agg regatio n and Disaggregation Work). Also, one way to look at disaggregated data is to view the underlying data that’s displayed in a table.
• How Aggregation and Disaggregation Work
How Aggregation and Disaggregation Work
When you place a measure on a shelf, Tableau automatically aggregates the data, usually by summing it. You can easily determine the aggregation applied to a field because the function always appears in front of the field’s name when it is placed on a shelf. For example, Profit becomes SUM(Profit).
This section discusses the following topics:
• Aggregating Data
• Disaggregating Data
• Exa mple – Aggregating a nd Disaggregating Data
• Aggregating Data
• Disaggregating Data
• Example – Aggregating and Disaggregating Data
Aggregating Data
You can change the aggregation of a field by selecting a different function from the field’s context menu. As shown below, all of the predefined aggregations are available from this menu.
Aggregating Measures
You can assign a different aggregation to every measure you place on a shelf. For example, you can aggregate Salesas a summation, Profit as a maximum, and Discount as an average.
You can change the aggregation state for all the measures on a worksheet by selecting the Analysis > Aggregate Measures menu item.
When all measures are disaggregated you see a mark for each row in the view. You cannot select specific marks to Keep Only, Exclude, or create a Set when all measures are disaggregated.
Aggregating Dimensions
When you aggregate dimensions, you create a new temporary measure column, so the dimension is now viewed as a measure. While you cannot apply all of the other predefined aggregations to a dimension, you can apply Dimension, Minimum, Maximum, and Count.
Disaggregating Data
Disaggregating your data allows you to view every row of the data source which can be useful when you are analyzing measures that you may want to use both independently and dependently in the view. For example, you may be analyzing the results from a product satisfaction survey with the Age of participants along one axis. You can aggregate the Age field to determine the average age of participants or disaggregate the data to determine at what age participants were most satisfied with the product.
You can disaggregate all measures in the view by selecting Analysis > Aggregate Measures.
Example – Aggregating and Disaggregating Data
This example includes several views of aggregated and disaggregated data created using the Sample – Superstore Sales data source. To create the views, follow these five steps:
1. Place the Sales measure on the Columns shelf and the Profit measure on the Rows shelf.
The measures are automatically aggregated as sums. The aggregation is indicated by the field names and by the tooltip. The values shown in the tooltip are the sales and the profit for the entire data source. That is, the summations are performed using every row in the data source.
2. Place the Product 1 – Category dimension on the Color shelf.
One way to show more data in your view is to disaggregate the measures. Another way is to show additional levels of detail. For example, placing the Product 1- Category dimension on the Color shelf separates the data into three marks—one for each dimension member—and then encodes the marks using color.
Although more marks are displayed, the measures are still aggregated. The single mark in the view indicates the sum of the sales and the sum of the profit for Office Supplies. If you were to sum the sales and profit values for the three marks, you would produce the values for the entire data set as given in the previous step of this example.
3. Place the Discount measure on the Filters shelf.
In the Filter Field dialog box select All Values to filter on the disaggregated measure.
Filter the data to only include discounts greater than 6% (0.06). Because Discount is disaggregated, Tableau applies the filter to each row in the data source before performing the aggregations for the Sales and Profitmeasures.
The view is shown below. The tooltip indicates that both the sales and the profit numbers are smaller than in the previous view. This is because data have been filtered out of the aggregation operation.
4. Change the aggregation of Sales to an average.
The measures are not required to have the same aggregation. Change the aggregation by selecting Averagefrom the field’s context menu.
The view is shown below. The field name and tooltips indicate the new aggregation.
5. Disaggregate the data.
All measures—except those placed on the Filters shelf—must have the same aggregation state. That is, they must all be either aggregated or disaggregated.
You change the aggregation state by selecting the Analysis > Aggregate Measures menu item.
The view is shown below. Disaggregating the data displays every row in the data source that passes through the filter. The tooltip shows the profit and sales for one particular row.
Calculated Fields
You might find that your data source doesn’t include all of the fields needed to answer your questions. For example, you might want to create a new calculated field called Profit that is the difference between the Sales and the Cost fields, or you might want to create a conditional statement that divides the Sales Budget field into values that are under budget and values that are over budget.
Tableau allows you to create a new calculated field by defining a formula that is based on data source fields and other calculated fields, and that uses standard functions and operators.
• How to Create a Calculated Field
• Copying and Pasting Calculated Fields
• Writing formulas in Tableau
• Example – Creating a Calculated Field
• Aggregate Calculations
• Example – Spotlighting Using Calculations
How to Create a Calculated Field
To create a new calculated field, select Analysis > Create Calculated Field, or select Create Calculated Field on one of the Data window title menu.
The Calculated Field dialog box opens.
To define the calculation do the following:
1. Specify a name for the new field.
2. Create a formula that defines the new field. Refer to Writing for mulas in Tableau for more information about how to define a formula.
3. When finished, click OK.
The new calculated field displays in either the Dimensions area or the Measures area of the Data window depending on the data type returned by the calculation. Calculations that return a string or date are dimensions, while calculations that return a number are measures. In the latter case, you can convert the measure to a dimension if you want to treat the calculated values as discrete rather than continuous.
Copying and Pasting Calculated Fields
Calculated fields are available to all sheets that use the same data source in a single workbook. In addition, you can copy and paste these custom fields between workbooks simply by right-clicking the field in the Data window and selectingCopy. Then in the new workbook, right-click the Data window and select Paste. You can copy and paste all custom defined fields such as calculated fields, ad-hoc groups, user filters, sets, and so on.
Writing formulas in Tableau
The formula editor has built-in coloring and validation to help you avoid syntax errors. As you write the formula, syntax errors are underlined with a red squiggly line. Hover over the error to see directions for fixing it. Also any errors with the caluclation are shown in a drop-down list. When the cacluation is valid, a green check mark is displayed.
When you are writing formulas, any part that displays in bold indicates that it will be computed locally within Tableau on the aggregated results. Any normal weight text will be computed at the database level.
Formuals are made up of the following parts:
1. Functions
The Functions area of the dialog box contains all the functions you can use to create a formula. The functions are organized into categories, which are available from the drop-down menu. By default all functions are displayed.
You can display a brief description for each function by clicking its name in the list box. Double-click a function to include it in a formula. Functions are colored black in the formula.
2. Fields
All data source fields and calculated fields are listed in the Fields area of the dialog box. Binned fields and setsare not listed because they cannot be used in calculations.
The field’s data type and the name display in the list. Use the drop-down menu to select a secondary data source and see its fields.
Double-click a field name to include it in a formula. You can also just type the bare field name. However, if the field name includes special characters such as spaces, it must be delimited with square brackets as inSUM([Store Profit]). A right bracket (]) can be doubled to include it in the field name itself. For example, the field name “Store Profit]” would be written as [Store Profit]]].
Fields are colored orange in the formula.
3. Operators
Operators are not available on the dialog box like functions and fields. Instead, you must manually type the operators into your formula. All standard operators such as addition (+), subtraction (–), multiplication (*), and division (/) are supported. Operators are colored black in the formula.
4. Parameters (optional)
Parameters are placeholders variables that can be inserted into calculations to replace constant values. When a parameter is used in a calculation, you can then use a parameter control to dynamically change the value. Parameters are colored purple in the formula.
5. Comments (optional)
You can insert custom comments for your calculations as a means of annotation for later review. To add a comment to a calculation type two forward slash characters into the formula pane.
For example:
Sales * Profit //John’s calculation
In this example //John’s calculation is a comment.
A comment starts at the two forward slashes (//) and goes to the end of the line. A multiline comment can be written by starting each line with two forward slashes (//). Comments are colored green in the formula.
Example – Creating a Calculated Field
In this example we will create a calculated field using Tableau formulas and use the new field in a data view. Then we’ll edit the field’s formula to create a new view, and finally delete the field from the Data window. This example uses the Sample – Superstore Sales (Excel) data source.
1. Create the view.
Select New Calculated Field on the Data window menu.
2. Complete the Calculation dialog box.
Name the new field Discount Ratio and enter the formula shown below.
IIF([Sales] !=0, [Disocunt]/[Sales],0)
You can type the formula by double clicking the field names in the Fields list and functions in the Functions list. You must type the operators (!= and /) manually. Note that the IIF statement is used to avoid dividing by zero.
The new field displays in the Measures area of the Data window because the calculation returns a number. You can use this new field just like any other field.
3. Add the calculation to the view.
Place Ship Mode on the columns shelf, Region on the Rows shelf, and Product 1- Category on the Color shelf. Then place the new calculation, Discount Ratio onto the Rows shelf. Note that you can treat the new calculation just like any other measure. For example, you can apply an aggregation to it. Below, Discount Ratio is aggregated as a maximum.
4. Edit the calculation.
You can change the field’s formula by right-clicking the field name in the Data window and selecting Edit or by selecting Analysis > Edit Calculation.
In the Calculated Field dialog box, change Sales to Order Quantity.
The view automatically updates after you click OK in the Calculated Field dialog box.
5. Delete the calculated field.
You can delete a calculated field by right-clicking the field name in the Data window and selecting Delete fromthe right-click context menu. Before deleting the field, you might want to save your workbook. If you do not save your work, the calculated fields will be lost.
Aggregate Calculations
Aggregate functions allow you to summarize data. As described in Ag gre gations, Tableau includes a variety of predefined aggregations such as summation and variance. An aggregate calculation allows you to define aggregations other than these predefined choices.
• About Aggregate Calculations
• How to Create an Aggregate Calculation
• Aggregate Calculations in a Disaggregated State
• Example – Aggregate Calculation
About Aggregate Calculations
Suppose you want to analyze the overall gross margin for every product in your data source. One way to do this is to create a new calculated field called Margin that is equal to the profit divided by the sales. Then you could place this measure on a shelf and use the predefined summation aggregation. In this scenario, Margin is defined as follows:Margin = SUM([Profit]/ [Sales])
This formula calculates the ratio of profit and sales for every row in the data source, and then sums the numbers. That is, the division is performed before the aggregation. However, this is almost certainly not what you would have intended because summing ratios is generally not useful.
Instead, you probably want to know the sum of all profits divided by the sum of all sales. That formula is shown below.
Margin = SUM( [Profit]) / SUM([Sales])
In this case, the division is performed after each measure is aggregated. An aggregate calculation allows you to create formulas like this.
How to Create an Aggregate Calculation
When a calculation uses an aggregate function, it’s called an aggregate calculation. You create an aggregate calculation by defining a new calculated field as described in Ho w to Create a Ca lculated Field. The formula will contain one or more aggregate functions. You can easily pick an aggregate function from the Calculation dialog box by selecting Aggregate from the Functions menu as shown below.
These functions are identical to the predefined aggregate functions listed in Ag gre gations.
The aggregate calculation appears with the letters AGG in front of it when it is placed on a shelf.
When you create an aggregate calculation, no further aggregation of the calculation is possible. Therefore, the field’s context menu does not offer any aggregation choices. However, you can disaggregate the field. Refer to Agg regate Calculati ons in a Disaggregated State for more information.
The rules that apply to aggregate calculations are:
• For any aggregate calculation, you cannot combine an aggregated value and a disaggregated value. For example, SUM(Price)*[Items] is not a valid expression because SUM(Price) is aggregated and Items is not. However, SUM(Price*Items) and SUM(Price)*SUM(Items) are both valid.
• Constant terms in an expression act as aggregated or disaggregated values as appropriate. For example:SUM(Price*7) and SUM(Price)*7 are both valid expressions.
• All of the functions can be evaluated on aggregated values. However, the arguments to any given function must either all be aggregated or all disaggregated. For example: MAX(SUM(Sales),Profit) is not a valid expression because Sales is aggregated and Profit is not. However, MAX(SUM(Sales),SUM(Profit))is a valid expression.
• An aggregate calculation is always a measure.
• Like predefined aggregations, aggregate calculations are computed correctly for grand totals. Refer to Gr and To tals and Aggregations for more information.
• ggregate Calculations in a Disaggregated State
• If an aggregate calculation is disaggregated, the calculation is modified in a way that depends on the functions used. Every function has a disaggregated substitute, as shown below.
Aggregation Function Disaggregated Substitute
AVG(data) data
COUNT(data) IIF(ISNULL(data),0,1)
COUNTD(data) IIF(ISNULL(data),0,1)
MAX(data) data
MIN(data) data
STDEV(data) Null
STDEVP (data) IIF (ISNULL (data), Null, 0)
SUM(data) data
VAR(data) Null
VARP (data) IIF (ISNULL (data), Null, 0)
• Note that STDEV and VAR are Nullbecause those functions return Null if there are fewer than two elements in a group that are not Null, and each group has size 1 when it is disaggregated. Refer to Ag gre gations for descriptions of the aggregation functions.
• Therefore, if you define an aggregate calculation called Margin that is equal to SUM(Profit)/SUM(Sales) and then disaggregate the data, it is interpreted as Profit/Sales.
Example – Aggregate Calculation
In this example you will use the Sample – Superstore Sales data source to create an aggregate calculation called Margin, and use the new field in a data view.
1. Select New Calculated Field on the Data window menu.
2. Define the calculation.
Name the new field Margin and enter the formula shown below.
IIF(SUM([Sales]) !=0, SUM([Profit])/SUM([Sales]), 0)
You enter the formula by selecting functions from the Functions area of the dialog box, and field names from theFields area of the dialog box. You must type the operators (!= and /) manually. Note that the IIF statement is used to avoid dividing by zero.
The new calculated field displays in the Measures area of the Data window where you can use it like any other measure.
A view using the new aggregate measure is shown below.
When Margin is placed on a shelf, its name is automatically changed to AGG(Margin), which indicates it’s an aggregate calculation. Additionally, the field’s context menu does not include any aggregation choices because aggregating a field that’s already aggregated is not possible.
Example – Spotlighting Using Calculations
Spotlighting is a term that applies a calculation that shows discrete thresholds based on the values of a measure. For instance, you might want to color-code sales so that those over 10,000 appear green and those below 10,000 appear red.A spotlighting calculation is just a special case of a calculation that results in a discrete measure. A discrete measure is a calculation that is a dependent variable (and therefore a measure), but which results in a discrete result (as opposed to a continuous result). Thus the name discrete measure. Here is an example:
The formula in this example defines a discrete measure called “Sales Spotlight.” Discrete measures always appear with a blue “abc” icon in the Data window. The example above is a measure because it is a function of another measure. It’s discrete because it produces discrete values (“Good” and “Bad”) as a result rather than continuous values like numbers. Here is an example of this categorical measure in use:
Here the “sales spotlight” field is on the Color Shelf. It appears with the “AGG” prefix because it is an aggregate calculation. Values above 10,000 and below 10,000 are assigned different colors. This type of discrete highlighting is often called spotlighting.
Table Calculations
Another kind of calculation is a table calculation. Table Calculations are computations that are applied to the values in the entire table and are often dependent on the table structure itself. For example, in a sales environment, you can use table calculations to compute the running total of sales across a specified date range or to compare multiple months of sales and compute each month’s contribution to the total sales.
All table calculations are computed locally using the values you see in the table. This means that computing a moving average for a measure that is aggregated as an average results in an average of the averages.
You can add table calculations to your view using either the predefined quick calculations or by specifying a custom definition.
• Understanding Table Calculations
• Quick Table Calculations
• Defining Basic Table Calculations
• Secondary Table Calculations
• Customizing Table Calculations
Understanding Table Calculations
Table calculations are computations that are applied to the values in the table. These computations are unique in that they usse data from multiple rows in the database to calculate a value. To create a table calculation, you need to define both what you want to compute and what to compute along. For example, when defining a running sum calculation for several years you are computing a running sum along the Date field. These are defined in the calculated field dialog box using the Calculation Type and Calculate Along drop-down menus.
The definition of what to compute along has two parts: addressing fields and partitioning fields.
• Addressing and Partitioning
Addressing and Partitioning
The addressing fields define what part of the table you are computing along. The partitioning fields define how to group the calculation. In the example of a running sum of product sales across several years, the addressing field is the Date field while the parititioning field is the product field. When youd define the addressing for a table calculation, all the other fields are used for partitioning.
You can specify the addressing in the Table Calculation dialog box. The addressing can be relative to the table structure or a specific field. Each addressing option is described below.
Table (Across)
This option sets the adressing to compute along the entire table moving horizontally through each partition. For example, the view below shows quarterly sales by region and product category. When a calculation addressing is set to Table Across, the fields that span horizontally across the table are the addressing fields (Category and Region). All the other fields (Year, Quarter) are partitioning. The addressing fields are shown in orange while partitioning fields are shown in blue.
That means that each partition will be the combination of Year and Quarter.
Table (Down)
This option sets the addressing to compute along the entire table moving vertically through each partition. For example, the same view from above is shown below with the addressing set to compute along Table Down. The fields that span vertically (Year, Quarter) are now the addressing fields and the rest of the fields are partitioning (Category and Region). The addressing fields are shown in orange while partitioning fields are shown in blue.
That means that each partition is the combination of Category and Region.
Table (Across then Down)
This option sets the addressing to compute across the entire table horizontally and then down the table vertically. This means that both the fields that span across the table and down the table are addressing fields.
That means that the entire table is the partition. The computation will compute across, move to the next row and continue to compute across, and so on.
Pane (Across)
This option sets to compute across the pane horizontally. The fields that span across the pane horizontally are the addressing fields. However, the fields that separate the panes are now partitioning fields. In the example below Category becomes a partitioning field along with Year and Quarter. Region is the addressing field.
That means that the combination of Year, Quarter, and Category is the partition.
Pane (Down)
This option sets the addressing to compute down the table within the pane. The fields that separate the pane (Category and Year) are partitioning fields. In addition, the Region field becomes a partitioning field while the Quarter field is the addressing field.
That means that the combination of Year, Category, and Region is the partition.
Pane (Across then Down)
This option sets the addressing to compute across within the pane, then move to the next row and continue to compute across. The addresssing fields are both the fields that run across the table horizontally and down the table vertically (Region and Quarter). The partitioning fields are the fields that define the pane (Category and Year).
That means that the combination of Category and Year make up the partition.
Cell
This option sets the addressing to the indvidual cells in the table. All fields become partitioning fields. This option is generally most useful when computing a percent of total calculation.
That means that the partition is the combination of Category, Region, Year, and Quarter.
Individual Fields
This option sets the addressing to compute across the field you specify. The benefit of this option is that you get absolute control over how the calculation will be computed. Be careful though, because, addressing on an individual field means that when you rearrange the table, the calculation may no longer match the table structure.
Advanced
The advanced option lets you specify multiple fields to act as the addressing fields. When you select Advanced another dialog box opens where you can specify one or more fields to act as addressing fields. Then you can specify how to order those fields.
For example, in the view below the addressing fields are set to Category and Year. These are ordered by SUM(Sales) Descending. That means that the combination of Quarter and Region create the partition. Q1 Central exists four times in the table, and that is the partition.
Because the order is set to SUM(Sales), the calculation is computed based on their SUM(Sales) values from highest to lowest.
Quick Table Calculations
You can add common table calculations to your view using the Quick Table Calculations menu item on the field context menus. These quick calculations are predefined table calculations based on the most common scenarios.
To add a quick table calculation:
1. Right-click the measure you want to use in the table calculation and select Quick Table Calculation.
2. On the sub-menu select one of the following options:
o Running Total
o Difference
o Percent Difference
o Percent of Total
o Moving Average
o Year to Date (YTD) Total
o Compound Growth Rate (CAGR)
o Year over Year Growth
o Year to Date (YTD) Growth
After adding the quick table calculation to the view, you can edit the definition by selecting Edit Table Calculation on the field’s context menu
efining Basic Table Calculations
When you add a Table Calculation to the view, you need to specify the parameters that define the formula used in the computation. All of these parameters are set in the Table Calculation dialog box.
To manually define a table calculation:
1. Right-click the measure you want to use in the computation and select Add Table Calculation.
2. In the Table Calculation dialog box, select one of the following types of calculations from the drop-down menu at the top:
o Differ ence From Calculation – compute the difference between two specified values.
o Percent Differe nce From Calculation – compute the difference between two specified values as a percentage.
o Percent From Calc ulation -compute each value as a percentage of another specified value.
o Percent of Total Cal culation – compute each value as a percentage of the total measure.
o Runn ing Total Calculation- compute cumulative totals along a specified dimension.
o Movi ng Calculation – summarize a range of values using the specified aggregation to smooth short fluctuations and reveal long term trends.
3. Define the formula using the drop-down lists in the bottom half of the dialog box. Learn more about how to define each type of calculation by selecting it in the list above.
4. When finished click OK. The measure is now marked as a table calculation and all the relevant values in the view are computed using the table calculation.
• Difference From Calculation
• Percent Difference From Calculation
• Percent From Calculation
• Percent of Total Calculation
• Running Total Calculation
• Moving Calculation
• Difference From Calculation
• Use this type of calculation to compute the difference between two specified values in the table along a certain dimension. For example, compute the difference between 2006 and 2007 quarterly sales for four different customer segments.
• When defining a Difference From calculation, you need to specify a dimension or table structure to compute across, the dimension level to use in the computation (this is only required if you are computing across a dimension), and a value to compare the current value to. The following is an example of a Difference From calculation.
• Example: Difference From Calculation
• The table below shows the 2006 and 2007 quarterly sales numbers for several different customer segments of a superstore.
•
•
•
• To compute the difference between 2008 and 2009 sales, you can define a table calculation using the definition shown below.
•
•
•
• The difference is calculated along the Order Date dimension at the level of year because we are comparing 2008 sales to 2009 sales. The table now displays the difference between each quarter in 2009 and the corresponding quarter in the previous year. Notice that there are no values for 2008. That’s because there are no previous years to compute the difference from. You can hide that column without affecting the calculation.
•
•
•
• The view below may be more clear. It shows both the Difference From calculation and the Total Sales (before the computation). You can see that in the first quarter of 2009 the total sales was $203,761 while in the same quarter in 2008 the total sales was $191,307. The difference between these two values is $12,454.
•
•
•
• Note:
• You can add a Difference From calculation to your view quickly by right-clicking the measure you want to use in the computation and selecting Quick Table Calculations > Difference. This quick calculation computes the difference between values across rows where each difference is calculated against the previous value. Refer to Quick Ta ble Calc ula tions.
• Percent Difference From Calculation
• Use this type of calculation to display the rate of change between two specified values in the table by computing the difference as a percentage. A common use of this type of calculation is to compute the percent gain year after year (CAGR).
• Similar to the Difference From calculation, to define a Percent Difference From calculation you need to specify a dimension or table structure to compute across, the dimension level to use in the computation (this is only required if you are computing across a dimension), and a value to compare the current value to. The following is an example of a Percent Difference calculation.
• Example: Percent Difference From Calculation
• The table below shows the profit for several customer segments over four years. Looking at the view, we can see that there was a drop in profit in the Small Business and Consumer segments in 2007.
•
•
•
• When we view this same view using a Percent Difference From calculation, it becomes clear that Small Business segment rebounded quite dramatically in 2008.
•
•
•
• You can define a Percent Difference From calculation like this using the table calculation definition shown below.
•
•
•
• The difference is calculated along the Order Date dimension at the Year level because we are comparing year after year profit. Each value in the view is a difference of the previous year. The view below shows each year as a difference of 2006 Profit. You can see that the Corporate segment is the only one to be above the 2006 profits in 2009.
•
•
•
• Note:
• Percent Difference From calculations are commonly used to calculate compound growth rates and year over year growth. You can quickly add these calculations by right-clicking the measure you want to use in the calculation and selecting the calculation on the Quick Table Calculation sub-menu. Refer to Quick Ta ble Calc ula tions.
• Percent of Total Calculation
• Use the Percent of Total calculation to compute the percentage a specified value contributes to the total. For example, you can use this type of calculation to view the contribution each member in a sales team makes to the total company sales each quarter.
• For a Percent of Total calculation you must specify a dimension or table structure to compute across and the dimension level to use in the computation. The dimension level is only necessary if you have chosen to compute across a dimension rather than a table structure. The following is an example of a Percent of Total calculation.
• Example: Percent of Total Calculation
• In this example, imagine that we are analyzing the performance of several stores. The view below shows the sales performance for three different regions. From the view we can see that the Southern region (orange marks) has the highest sales.
•
•
•
• When we add the Percent of Total table calculation to the view, we can see that the South region accounts for just over 27% of the total sales.
•
•
•
• The table calculation was computed using the definition shown below. The total was computed across each row in the table.
•
•
•
• Note:
• You can add a Percent of Total calculation quickly to your view by right-clicking the measure you want to use in the computation and selecting Percent Total on the Quick Table Calculations sub-menu. Refer to Quick Ta ble Calc ula tions.
Running Total Calculation
Use the Running Total calculation to compute a cumulative total across a dimension or table structure. For example, you can use this type of calculation to calculate the cumulative sales for each quarter for several years.
When you define a running total calculation, you need to specify an aggregation to use when summarizing the values. For example, the most common aggregation will be sum so you can see the summation of values, but sometimes you may want to use average or another aggregation. You also need to specify the dimension to compute a running total across. This can either be an actual dimension in the data source or a table structure like rows or columns. Finally, you need to specify when to restart the at zero and begin totaling again. The following is an example of a Running Total calculation.
Example – Running Total Calculation
The view below shows the total quarterly sales from 2006 to 2009.
While it is useful to see each quarter’s sales, you may also want to see the cumulative totals for each quarter in the year. To create this kind of view we can add a Running Total calculation. The view below shows the running totals for each quarter restarting at zero for each year. That means that the Quarter 4 shows the total sales for that year.
This calculation was defined by the formula shown below. We are summarizing values as a sum along the Order Date dimension restarting at zero every Year.
Note:
You can add a Running Total calculation to your view easily using the Quick Table Calculations menu. Right-click the measure you want to use in the calculation and select Quick Table Calculations > Running Total. Refer to Quick Ta ble Calc ula tions.
Moving Calculation
A moving calculation is typically used to smooth short term fluctuations in your data so that you can see long term trends. A good example is when you are looking at securities data. There are so many fluctuations every day that it is hard to see the big picture through the daily ups and downs. You can use a moving calculation to define a range of values to summarize using an aggregation of your choice.
When you define a moving calculation you must first specify the aggregation you want to use when summarizing that data. The most common aggregation for this type of calculation is an average. Next you need to specify the dimension to summarize across. You can select a table structure such as Rows or Columns or an actual field in your data source. Once you have selected a dimension, define the number of values before the current value and the number of values after the current value to include in the summary. You can also decide whether to include the current value using the checkbox on the right. The following is an example of a Moving Calculation.
Example – Moving Calculation
The view below shows the the discounts given at a superstore along a continuous date axis. As you can see, it is very difficult to see any kind of trend in this view.
However, if you add a Moving Average, the view becomes much more manageable.
This calculation was defined by the formula shown below. The values are summarized as an average along the rows in the view. Each value is an average of the seven days surrounding the current value (four days before and three days after). Note that we have opted to include the current value.
Note:
You can add a Moving Average to your view quickly using the Quick Table Calculations menu. Right-click the measure you want to use in the calculation and select Quick Table Calculations > Moving Average. By default this quick calculation will add a moving average across the rows in the view, summarizing the previous two values including the current value. Refer to Quick Ta ble Calc ula tions.
calculations can be very useful when you want to perform a calculation that applies to all of the data in the table. Most of the time you will only need to add a single calculation such as Difference From or Running Total. However, you may sometimes want to combine two calculations so that you perform one and then perform the next on the results. For example, when calculating the Year to Date Growth, you first need to calculate the cumulative totals and then calculate the percent difference each total is from the previous year. You can add a secondary calculation to Running Totals and Moving Calculations by selecting Perform secondary calculation on the result in the Table Calculation dialog box.
Customizing Table Calculations
Table calculations are a special type of calculated field that computes on the local data in Tableau. While you can use the built-in table calculations such as Percent of Total, Difference From, Running Total, and so on; the functions required to define these calculations are also available for use in your own custom calculated fields. Customizing table calculations allows you to compute values such as the difference in number of orders this quarter versuse an average quarter, total sales for regions that have above aaverage margin, time since first click on a website, average temperature based on the last three days weighted at 10%. 40%, and 50%, and so much more.
An easy way to become familiar with the Table Calculation functions is to add a basic table calculation and then click the Customize button in the lower left corner of the Table Calculation dialog box.
When you click Customize, the Calculated Field dialog box opens showing the formula for the calculation. You can see that it uses special functions.
After you customize the calculated field, the changes are not saved until you click OK in the Calculated Field dialog box and in the Table Calculation dialog box. The new table calculation field is added to the Data window.
When you use that field in other views, it uses the default addressing and partitioning specified when the table calculation was created. You can change the addresssing by right-clicking the field and select an option on the Compare To context menu.
Table calculation formulas much use aggregated data throughout the entire formula. When you are referring to a dimension in the formula you can use the MIN([Dimension]) or MAX([Dimension]) aggregations. However, this trick only works if the view is grouped by that dimension. That is, when there is a single dimension value for the row in question. For example, the view below shows several customer segments. Each segment corresponds to 4 regions. The MIN([Region]) returns “Central” even though there are three other regions.
You can instead use the ATTR([Dimension]) aggregation. When you use ATTR the dimension value is used when you are grouping by the dimension. If there are multiple values it shows an asterisk. Nulls are ignored. The same view is shown below using ATTR([Region]).
The ATTR aggregation is especially useful when you are working with multiple data sources on a single sheet.
Binned Data
Sometimes it’s useful to organize the values of a measure into bins. For example, suppose you have a measure that holds the ages of customers ranging from 18 to 90. If you wanted to analyze how customer value breaks down by different age groups, you would bin the data. Also, to create a histogram you must first bin data.
In Tableau, bin data by highlighting a numeric dimension or measure in the Data window and selecting Create Bins from the context menu.
When you bin a measure you create a new dimension. That’s because you are creating discrete categories out of a continuous range of values. The following example walks you through creating a histogram using binned data.
• Example – Creating a Histogram with Binned Data
Example – Creating a Histogram with Binned Data
Histograms are one way to display the distribution of values in a field. In Tableau, you can create a histogram by binning the values of a measure and then creating a view based on the measure and its binned values. This example uses the Sample – Superstore Sales data source.
To create a histogram based on binned data:
1. Select the Sales measure in the Data window and select Create Bins on the right-click context menu.
2. Complete the Create Bins dialog box.
When you bin a measure, you create a new field. The new field is a binned version of the original field. Specify the name of the new field and the size of each bin. To help you determine the best bin size, press the Loadbutton to display the range of values of the measure.
The binned field appears in the Dimensions area of the Data window because the bins are treated as discrete categories.
3. Place the Sales measure on the Rows shelf.
The measure is automatically aggregated as a summation, and an axis is created with a label given by the field name.
4. Place the Sales (bin) dimension on the Columns shelf.
Row headers are created with labels given by the dimension member names.
Note:
Notice that all bins are of equal size. If you want to create variable sized bins, you can create a calculation using the using the CASE function.
The view is shown below.
Each bin acts as an equal-sized container that summarizes data for a specific range of values. Each bin label designates the lower limit of the range of numbers that is assigned to the bin. Note that the lower limit is inclusive. For example, the bin labelled 1K contains numbers greater than or equal to 1,000, but less than 2,000.
Note:
This example shows how to build a histogram manually. You can also create a histogram automatically. Do this by (1) selecting a measure in the Data window; (2) clicking the Show Me! button on the toolbar; (3) selecting the histogram option.
Totals
You can automatically compute grand totals and subtotals for the data in a view. By default Tableau uses the underlying data to compute totals.
• Grand Totals
• Subtotals
Grand Totals
Any view in Tableau can include grand totals. For example, in a view showing the average profit for each product and year, you can turn on grand totals to also see the average profit for all products and all years.
• How to Turn on Grand Totals
• Grand Totals and Aggregations
• Example – Grand Totals and Aggregations
How to Turn on Grand Totals
You can calculate grand totals by selecting one of the Grand Totals options on the Table menu. The grand totals are added as an additional row or column to your table.
The following rules dictate whether you can turn on grand totals:
• The view must have at least one header – Headers are displayed whenever you place a dimension on theColumns shelf or the Rows shelf. If column headers are displayed, you can calculate grand totals for columns. If row headers are displayed, you can calculate grand totals for rows.
• Measures must be aggregated – The aggregation determines the values displayed for the totals. Refer to Gr and To tals and Aggregations for more information.
• Grand Totals cannot be applied to continuous dimensions.
You can also display totals for graphical views of data. In the figure below, only column totals are calculated because the table contains only column headers.
Grand Totals and Aggregations
Grand totals are computed using the aggregation of each measure. For example, if you are totaling the SUM(Profit) for several products, the grand total will be the sum of the sums of profit. For aggregations such as SUM, you can easily verify the grand total because a summation of a group of sums is still a summation. However, be aware that your results may be unexpected when using other aggregations, especially custom aggregations. For example, when looking at the average sales for several products, the grand total will be the average of the averages rather than the average of all sales. You can verify any calculation such as an aggregation or a grand total by viewing the underlying disaggregated data.
The following table summarizes the standard aggregations and the grand totals that are calculated.
Aggregation Calculation Description
Sum The grand total using sum is the sum of the values shown in the row or column.
Average The grand total using average is the averages shown in the row or column.
Minimum The grand total using minimum is the minimum value shown in the row or column.
Maximum The grand total using maximum is the maximum value shown in the row or column.
Standard Deviation The grand total using standard deviation is the standard deviation values shown in the row or column.
Variance The grand total variances are not the variances of the rows and columns in which they reside. Instead, the calculations are based on the underlying data behind the row or column.
Count & Count Distinct The grand total counts are the counts of the rows and columns in which they reside.
Example – Grand Totals and Aggregations
The figure shown below is a text table that displays the sales aggregated as a sum. The grand total for the Jumbo Box shipped by Delivery Truck is $2,814,305. Tableau calculates this number by summing all the rows in the data source that are associated with the Jumbo Box and Delivery Truck fields. You can easily verify the number by summing the values for the Central, East, South, and West regions.
The intersection of the grand total for columns and rows represents the grand total for the entire table. Tableau calculates this number by summing the sales for every row in the data source. Because the aggregation is a summation, you can verify this number by summing the grand totals for rows or for columns.
The figure shown below is a text table that displays the sales aggregated as an average. The grand total for the Jumbo Box is $5,350. Tableau calculates this number by averaging all the rows in the data source that are associated with the Jumbo Box and Delivery Truck fields. You cannot verify this number by averaging the values for the Central, East, and West regions.
The intersection of the grand total for columns and rows represents the grand total for the entire table. Tableau calculates this number by averaging the sales for every row in the data source. Because the aggregation is an average, you cannot verify this number by averaging the grand totals for rows or for columns. To verify the grand total you need to average the rows in the data source, which are not visible in the table. You can display the rows by right-clicking in the table and selecting View Data on the context menu.
Subtotals
Any data view in Tableau can include subtotals. For example, you may have a view containing the total sales for two product types broken down by specific products. In addition to seeing the sales for each product you may want to see the total sales for each product type.
To calculate subtotals, identify the field you want to subtotal (Product Type in the above example) and select Subtotals from its context menu. The subtotal is the total of all the members in that field.
When you turn on subtotals for a specific field, the totals will change based on where that field is in the view. Consider the following example. The view below shows the sales for different product types sold across four different markets. Each product type is broken down by specific products. In addition, subtotals are turned on so that the view shows the total sales for each product type.
Now let’s move the Product Type field from the Rows shelf to the Columns shelf. The view still shows the sales for four different product types; but now, the product types are broken down by market. Because subtotals were turned on for the Product Type, the subtotals are the sum of the sales completed in each market.
Percentages
Any analysis in Tableau can be expressed in terms of percentages. For example, rather than viewing sales for every product, you might want to view each product’s sales as a percentage of the total sales for all products.
You calculate percentages by selecting the Analysis > Percentages Of menu item. When you do this, all measures on the worksheet are displayed as a percentage based on all the table data.
Note:
The percentage options on the Analysis menu correspond to the percentage table calculations. When you select a percentage option, you are actually adding a Percent of Total table calculation. Refer to Table Calculations to learn more.
• About Percentages
• Percentages and Aggregations
• Percentage Options
About Percentages
There are two factors that contribute to the percentage calculation:
• The aggregation – Percentages are calculated on the basis of the current aggregation for each measure. Refer to Percen tages and Aggregations for more information.
• The data to which you compare all percentage calculations – Percentages are a ratio of numbers. The numerator is the value of a given mark. The denominator depends on the type of percentage you want, and is the number to which you compare all your calculations. The comparison can be based on the entire table, a row, a pane, and so on. By default, Tableau uses the entire table. Other percentage calculations are available via thePercentage of menu item. Refer to Per centage Options for more information.
The figure below is an example of a text table with percentages. The percentages are calculated with the Salesmeasure aggregated as a summation, and are based on the entire table.
Percentages and Aggregations
Percentages are computed on the basis of the aggregation for each measure. Standard aggregations include summation, average, and so on. Refer to Ag gre gations for more information.
For example, if the aggregation applied to the Sales field is a summation, then the default percentage calculation (percent of table) means that each number displayed is the SUM(Sales) for that mark divided by the SUM(Sales) for the entire table.
In addition to using predefined aggregations, you can use custom aggregations when calculating percentages. You can define your own aggregations by creating a calculated field. Once the new field is created, you can use percentages on the field as you would any other field. Refer to Aggre gate Calculations for more information.
Percent calculations can also be applied to data that are disaggregated. In this case, all values are expressed as the percentage of a summation. You cannot choose an alternative aggregation.
• Example – Percentages and Aggregations
• Example – Percentages and Aggregations
• The view shown below is a nested bar chart created using two dimensions and a measure that is aggregated as a maximum. Additionally, the data are color-encoded by a dimension and the default percentage calculation has been applied. Notice that the axis labels are modified to reflect the percent calculation.
• The tooltip reveals that the maximum sales for furniture in the east in 2005 is 31.6% of the maximum for the entire table. What is the maximum for the table? You can see by inspection that the maximum occurs in the South in the year 2005. The tooltip for this bar segment would reveal a maximum sales of 100%.
•
•
•
• The next view displays two disaggregated measures as a scatter plot. Again, the default percentage calculation has been applied as reflected by the modified axis labels.
• The tooltip shows that the selected data point has a profit of -0631% and a sales of 0.1636%. Note that the percentage calculations are based on the entire data source.
•
•
Percentage Options
Computing a percentage involves specifying a total on which the percentage is based. In Tableau, the default percentage calculation is based on the entire table. However, you can change the default by selecting a different percentage option from the Analysis > Percentage of menu. Select from the following options:
• Per cent of Table
• Perc ent of Column
• Perc ent of Row
• Percen t of Pane
• Per cent of Row in Pane
• Percent of Co lumn in Pane
The option you choose is applied uniformly to all measures that appear on a worksheet. For instance, you cannot choose Percent of Column for one measure and Percent of Row for another.
If you are unsure what the current percentage calculation means, display the grand totals. This provides more information about each row and column. For example, if you select Percent of Row while displaying grand totals, you will see that the total for each row is exactly 100%. Refer to Gr and Totals for more information on grand totals.
The percent calculation options are described in the following sections. In each case, the grand totals are displayed as well.
• Percent of Table
• Percent of Column
• Percent of Row
• Percent of Pane
• Percent of Row in Pane
• Percent of Column in Pane
• ercent of Table
• When you select Percentage of Table, each measure on the worksheet is expressed as a percentage of the total for the entire worksheet (table). For example, Technology in the East region accounts for 2.50% of total sales in 2006. The grand totals for rows show that 2009 accounts for 24.95% of the total sales. Summing the grand totals for rows or for columns yields 100% of the total.
•
•
•
• An equivalent graphical table is shown below.
•
•
• Percent of Column
• When you select Percentage of Column, each measure on the worksheet is expressed as a percentage of the total for the column. For example, Technology in the East region accounts for 6.62% of total sales in the 2006. 2006 is the column in this case.
•
•
• Percent of Row
• When you select Percentage of Row, each measure on the worksheet is expressed as a percentage of the total for the row. For example, Technology in the East region accounts for 21.72% of technology sales in the east across all years. East is the row in this case.
•
•
• Percent of Pane
• When you select Percentage of Pane, each measure on the worksheet is expressed as a percentage of the total for the pane. This option is the same as Percent of Table when the table consists of only a single pane.
• This table consists of several panes. Each pane encompasses one product category and all four years. Therefore, the table is one pane wide and three panes high.
•
•
• Percent of Row in Pane
• When you select Percentage of Row in Pane, each measure on the worksheet is expressed as a percentage of the total for a given row within a pane. This option is the same as Percent of Row when the table is only a single pane wide.
• Note:
• If you place Measure Names as the inner dimension on the Columns shelf, Tableau will return 100% for each mark because you cannot total up the values for multiple measure names. For example, you can’t total up the values for SUM(Sales) and SUM(Profit).
• The grand total for columns is always 100%. This represents the sum of a given row within a pane.
•
•
• Percent of Column in Pane
• When you select Percentage of Column in Pane, each measure on the worksheet is expressed as a percentage of the total for a given column within a pane. This option is the same as Percent of Column when the table is only a single pane high.
• Note:
• If you place Measure Names as the inner dimension on the Rows shelf, Tableau will return 100% for each mark because you cannot total up the values for multiple measure names. For example, you can’t total up the values for SUM(Sales) and SUM(Profit).
• The grand total for rows is always 100%. This represents the sum of a given column within a pane.
•
•
Parameters
Parameters are dynamic values that can replace constant values in calculations. For example, you may create a calculated field that returns true if Sales is greater than $500,000 and otherwise return false. You can replace the constant value of “500000” in the formula with a parameter. Then using the parameter control you can dynamically change the threshold in your calculation.
• Creating Parameters
• Editing Parameters
• Using Parameters in Calculations
• Parameter Controls
• Example – Parameters
Creating Parameters
You can create a new parameter based on a selected field, or you can create a new parameter from the Calculated Field dialog box. Follow the instructions below to create a new parameter.
1. Do one of the following:
o In the Calculated field dialog box, click the Create link at the top of the list of parameters.
o In the Data window, right-click a field to base the parameter on and select Create Parameter.
o Open the Data window menu using the drop-down arrow in the upper right corner and select Create Parameter.
2. In the Create Parameter dialog box, give the field a Name and optionally write a Comment to describe the parameter.
3. Specify the data type for the values it will accept. Parameters can be a float (non integer number), integer, string (text), boolean (true/false), date, or date & time.
4. Specify a current value. This is the default value for the parameter.
5. Specify the display format to use in the parameter control.
6. Specify how the parameter will accept values. You can select from the following options:
o All – the parameter control is a simple type in field.
o List – the parameter control provides a list of possible values for you to select from.
o Range – the parameter control lets you select values within a specified range.
The availability of these options is determined by the data type. For example, a string parameter can only accept all values or a list. It does not support a range.
If you select List, you must specify the list of values. Click in the left column to type a value. Each value can also have a display alias. You can copy and paste a list of values by clicking Paste from Clipboard. Alternatively you can Add the members of a field as the list of values by selecting Add from Field.
If you select Range you must specify a minimum, maximum and step size. For example, you can define a date range between January 1, 2010 and December 31, 2010, with the step size set to 1 month to create a parameter control that lets you select each month in 2010.
7. When finished, click OK.
The parameter is listed in the Parameters section at the bottom of the Data window.
It is also available in the Parameters list in the Calculated Field dialog box. Parameters are global across the workbook and can be used in any worksheet.
Editing Parameters
You can edit parameters from the Data window or the parameter control. Follow the instructions below to edit a parameter:
1. Do one of the following:
o Right-click the parameter in the Data window and select Edit.
o Select Edit Parameter on the parameter control card menu.
2. In the Edit Parameter dialog box, make the modifications as necessary.
3. When finished, click OK. The parameter is updated along with any calculations that use it.
Note:
You can delete a parameter by right-clicking it in the Data window and selecting Delete. Any calculated fields that use the deleted parameter will become invalid.
Using Parameters in Calculations
Parameters give you a way to dynamically modify values in a calculation. Rather than manually editing the calculation (and all dependent calculations), you can use a parameter. Then when you want to change the value, you open the parameter control, change the value, and all of the calculations that use that parameter are updated.
A list of parameters are available in the Calculated Field dialog box. Simply double-click the parameter in the Parameters list to add it to your formula.
Because you often don’t realize you need a parameter until you are authoring the calculation, you can create parameters directly from the Calculated Field dialog box. Click the Create link at the top of the Parameters list. Refer to Creating Parameters for detailed instructions on creating parameters.
Similar to fields, parameters are written within square brackets in the formula. Parameters are shown as purple in the formula editor.
Parameter Controls
The parameter control is a worksheet card that lets you modify the parameter value. Parameter controls are very similar to quick filter cards in that they contain controls that modify the view. You can open parameter controls on worksheets and dashboards and they are included when you save to the web or publish to Tableau Server.
Open the parameter control by right-clicking the parameter in the Data window and selecting Show Parameter Control.
Like other cards, Parameter Controls have a menu that you can open using the drop-down arrow in the upper right corner of the card. Use this menu to customize the display of the control. For example, you can show a list of values as radio buttons, a compact list, a slider, or a type in field. The options available on this menu depend on the data type of the parameter as well as whether it accepts All, a List, or a Range of values
Example – Parameters
This example uses a data source that contains home sale information. The example below shows a view of recently sold houses in the Seattle area.
Follow the steps below to create a parameter that is used to color the houses based whether they fit into a specific budget.
1. Open the calculated field dialog box by selecting Analysis > Create Calculated Field.
2. Name the field “Within Budget.”
3. In the formula type a boolean expression using the IF function. The formula is shown below:
IF [Sale Price] >= 500000 THEN “No” ELSE “Yes” END
This formula will create a field that returns No if the sale price is over a budget of 500000. However, this threshold should be dynamic so that you can quickly change the budget without editing the calculation. We need to replace the constant value with a parameter.
4. Click the Create link at the top of the Parameters list in the Calculated Field dialog box.
5. Name the parameter “Budget.”
6. Select the Float data type because the values will be currency.
7. Set the Current Value to 500000. This is the default value for the parameter and just gives it a starting point.
8. Change the display format to Currency (Standard).
9. Allow a Range of values. Set the Minimum to 100,000 and the Maximum to 3,000,000. Set the Step size to 25,000.
This definition means that the budget can be between $100K and $3M in increments of $25K.
10. When finished, click OK.
11. Back in Calculated Field dialog box, delete the constant value of “5000000” in the formula. Then double-click the Budget parameter in the Parameters list to add it to the formula. The formula now looks like this:
IF [Sale Price] >= [Budget] THEN “No” ELSE “Yes” END
12. Make sure the calculation is valid and then click OK.
13. The new field is added to the Dimensions area of the Data window. Drag the Within Budget field to the Colorshelf.
14. Right-click the Budget parameter at the bottom of the Data window and select Show Parameter control.
By default the control is shown as a slider. Clicking the right arrow in the control will increase the budget by $25K.
The view below shows the budget set to $400,000. Orange house are within budget and blue are not.
Background Images
Background images are images that you display underneath your data in order to add more context to the marks in the view. A common use of background images is adding custom map images that correspond to a coordinate system in your data. For example, you may have data that corresponds to several floors in a building. You can use background images to overlay that data on the actual floor plan of the building to give more context. Other examples of using background images include showing a model of the sea floor, images of web pages for analyzing web logs, and even levels from video games to visualize player statistics.
While Tableau allows you to load dynamic maps from the online and offline provider, background images allow you to use your own custom images whether they are special maps or any other image that corresponds to your data.
• Adding Background Images
• Setting up the View
• Managing Background Images
Adding Background Images
When you add a background image to the view, you need to specify a coordinate system by mapping both the x and y axes to the values of fields in your database. If you are adding a map, the x and y axes should be longitude and latitude expressed as a decimal. However, you can map the axes to any relevant fields based on your own coordinate system.
To add a background image:
1. Select Data > Background Images.
2. In the Background Images dialog box, click Add Image.
3. In the Add Background Image dialog box do the following:
o Type a name for the image into the Name text box.
o Click Browse to navigate to and select the image you want to add to the background. You can also type a URL to link to an image hosted online.
o Select the field to map to the x-axis of the image and specify the left and right values. When adding a map, the longitude values should be mapped to the x-axis using decimal values (instead of degrees/minutes/seconds or N/S/E/W).
o Select the field to map to the y-axis of the image and specify the top and bottom values. When adding a map, the latitude values should be mapped to the y-axis using decimal values (instead of degrees/minutes/seconds or N/S/E/W).
o You can adjust the intensity of the image using the Washout slider. The farther the slider moves to the right, the more faded the image will appear behind your data.
4. You can specify the following options using the Options tab:
o Lock Aspect Ratio – select this option to maintain the original dimensions of the image for any manipulations of the axes. Deselecting this option allows the image’s shape to be distorted.
o Always Show Entire Image – select this option to avoid cropping the image when the data encompasses only a portion of the image. If you lock both the axis in a view, this option may be negated.
o Add conditions for when to show the image. Refer to Adding Show/Hide Conditions to learn more about defining conditions.
5. Click OK.
Note:
In order to make the marks in a view more visible when placed on top of a background image, each mark is surrounded by a solid contrasting color called a halo. You can turn mark halos off by selecting Format > Show Mark Halos. Refer to to learn how to change the color of the mark halos.
Setting up the View
After you add a background image, you need to build the view in a way that matches the x and y mappings you specified for the image. That is, the fields you specified as x and y must be on the proper shelves. Follow the steps below to set up the view correctly:
1. Place the field mapped to the x-axis on the Columns shelf.
If you are working with maps, the longitude field should be on the columns shelf. It may seem backward at first, however, the fields on the columns shelf determine the values distributed across the x-axis.
2. Place the field mapped to the y-axis on the Rows shelf.
If you are working with maps, the latitude field should be on the rows shelf. It may seem backward at first, however, the fields on the rows shelf determine the values distributed across the y-axis.
Managing Background Images
You can add several background images to the workbook and then select the image or images to make active on each sheet. The Background Images dialog box lists all of the images, the required fields, and whether they are visible. The visibility is determined based on whether the required fields are used in the current view. This section discusses the following way to manage your images:
• Editing an Image
• Enabling/Disabling Images
• Adding Show/Hide Conditions
• Removing an Image
Editing an Image
After adding a background image, you can always go back and edit the x and y field mappings as well as any of the options on the Options tab.
To edit an image:
1. Select Data > Background Images.
2. In the Background Images dialog box, select the image you want to edit and click Edit (you can also just double-click the image name).
3. In the Edit Background Image dialog box, make the changes to the image and click OK.
Enabling/Disabling Images
Although you can add multiple images to a workbook, you may want to only use a subset of the images for a particular set of worksheets. For example, you may want to show a map of the entire United States of America on one view, and maps of individual states in other views.
Use the checkboxes in the Background Images dialog box to enable and disable the images for the current worksheet. You can show several images by enabling multiple images on a single worksheet. For example, you may have several images that you want to tile in the background to make a larger background image.
To enable or disable a background image:
1. Select Data > Background Images.
2. In the Background Images dialog box, select the checkboxes next to the images you want enabled.
3. Click OK.
Adding Show/Hide Conditions
When you add a background image and enable it, the image will be shown automatically on any worksheet that has the required fields used in the view. To avoid showing an image on all the worksheets, you can specify show/hide conditions. Show/Hide conditions are conditional statements that you define to specify when to show the image. For example, you may have a floor plan image for a multi-story building. While each image is associated with the same coordinates (the corners of the building), you do not want to show the first floor map when looking at the third floor information. In this case, you can specify a condition to only show the first floor image when the Floor field is equal to one.
To specify show/hide conditions:
1. Select Data > Background Images.
2. In the Background Images dialog box, select the image you want to add a condition to and click Edit.
3. In the subsequent dialog box, select the Options tab.
4. Click the Add button at the bottom of the dialog box.
5. Select a field to base the condition on. In the example described above, the field is Floor.
6. Specify when you want to show the image by selecting a value of the field. For this example, one is selected.
7. Click OK.
A condition statement is added to the image. In the building floor plan example, the condition statement is Only show the image when Floor is equal to One.
8. Click OK twice to close the Background Image dialog boxes and apply the changes.
Note:
When you add multiple conditions the background image will only show when all conditions are met. For example, if a background image has two conditions on Property Name and Floor, it will only show when Property is Greenwood Estates and Floor is 3.
Removing an Image
When you no longer want to use a background image you can either disable it (refer to ) or remove it, making it unavailable to all worksheets.
To remove an image:
1. Select Data > Background Images.
2. In the Background Images dialog box, select the image you want to remove and click Remove.
3. Click OK.
Background Maps
When you are connected to data that contains location information you can automatically show the data on an online or offline map. Once a map is loaded, you can zoom in and out, pan around the map, or focus on specific regions using the Navigation tools. This section discusses how to build a view using Tableau’s background maps feature.
If you would like to overlay your data on a static image instead you can use a background image. Refer to Background Images to learn how.
• Geographic Roles
• Building a Map View
• Map Options
• Setting a Default Location
• Editing Locations
• Custom Geocoding
• Background Map Sources
• Map Storing and Working Offline
Geographic Roles
The first step to building a map view is to specify the fields that contain the location data. Tableau will automatically assign geographic roles to fields with common location names such as State, Country, and so on. You can manually assign geographic roles to fields that don’t use common names and weren’t automatically detected.
To assign a geographic role:
1. Right-click the field in the Data window that contains geographic data and select Geographic Role. Then select the type of data the field contains.
When you assign a field a geographic role it is marked in the Data window with a globe icon . What that means is that Tableau has automatically geocoded the information in that field and associated each value with a latitude and longitude value. Two automatic fields are added to the Measures area of the Data window: Latitude (generated) and Longitude (generated). These fields are available anytime you have use Tableau to geocode your data.
Below is a table describing the types of information that Tableau can geocode.
Geographic Role Description
Area Code U.S. Area Codes; numbers only. Examples: 206, 650, 415
CBSA U.S. Core Based Statistical Area. Example: Dallas-Fort Worth-Arlington, TX
City (English) Worldwide city names in English.
City (French) Worldwide city names in French.
City (German) Worldwide city names in German.
Country (FIPS 10) Worldwide countries using the U.S. Federal Information Processing Standard No. 10. Example: AF, CD, IZ
Country (Name – English) Worldwide country names in English. Examples: United States, Japan, Italy
Country (Name – French) Worldwide country names in French.
Country (Name – German) Worldwide country names in German.
Country 2 char (ISO 3166-1) Worldwide countries using the alpha 2 of the 3166 standard set by the International Organization for Standardization.Examples: AF, BH, UA
Country 3 char (ISO 3166-1) Worldwide countries using the alpha-3 of the 3166 standard set by the International Organization for Standardization.Examples: AFG, BHR, UKR
County U.S. county names. Example: King, Clark, Oneida
Latitude Latitude in decimal degrees. Only available for numeric fields.
Longitude Longitude in decimal degrees. Only available for numeric fields.
State (Abbreviation) United States, Canada, and Australia states and province abbreviations. Examples: WA, BC, AB
State (Name – English) Worldwide state and province names in English. Examples: California, Alberta, Berlin
State (Name – French) Worldwide state and province names in French.
State (Name – German) Worldwide state and province names in German.
ZIP Code/Postcode ZIP Codes and Postcodes for the United States, France, Germany, United Kingdom, Canada, Australia, and New Zealand.
Building a Map View
You can use the generated latitude and longitude fields to build map views simply by double-clicking a geographic field in the Data window. The Show Me! double-click rules will automatically add the generated Latitude and Longitude fields to the shelves and place the geographic field on the Level of Detail shelf.
The marks in the view correspond to the field on the Level of Detail shelf. For example, above the State field is used in the view so there is a single mark for each state. As you add more geographic fields to the Level of Detail shelf, the marks in the view will be broken down by the members of those fields. Refer to idd8dc0ebe-6e0c-4dc7-9184-1598358d99d7.html#i999891 to learn more about how this shelf works.
When you first create a map view you have several options available including a variety of layers such as streets and highways, place names, and so on. Refer to Ma p Options to learn more.
Map Options
When you load a map there are several options to help you control the display of the map. For example, you can select between the normal, grayscale, or dark map styles using the drop-down menu at the top of the Map Options card.
You can turn on layers such as streets and highways, state and county boundaries, and place names. You can also apply data layers containing census information such as population and average household income. Finally you can adjust the washout to make the map fade into the background more. All of these options are set from the Map Options card, which displays by default when you create a map image. You can also turn it on by selecting View > Other Cards > Map Options.
• Map Layers
• Data Layers
• Washout
Map Layers
Tableau online maps provide several layers that can mark points of interest on the map. For example, overlay streets and highways, county boundaries, and more. These layers are displayed in the Map Options card.
Turn on layers by selecting them in the Map Options card.
Many layers are only visible at specific zoom levels. Refer to Inspecting Dataid514fd341-bcda-4f79-af02-3d542d4dfeb4.html#i1040171 to learn more about navigating the view. Each of the map layers are described in the table below.
Layer Name Description
Landcover Shades wilderness areas and parks to give the map more depth.
Streets and Highways Marks freeways and highways as well as small city streets. This layer includes the highway and street names as well.
Light Borders & Names Shows country names, country borders, state names, state borders. and major roads as light gray.
Country Borders Highlights country borders in a darker gray.
Cuontry Names Hilights country names in a darker gray.
State/Province Borders Highlights state and province borders.
State/Province Names Highlights state and province names.
US County Borders Highlights U.S. county borders.
US County Names Highlights U.S. county names.
Zip Code Boundaries Marks U.S. zip code boundaries. You must zoom in to see this layer.
Zip Code Labels Shows labels for U.S. zip codes. You must zoom in to see this layer.
Area Code Boundaries Marks the U.S. area code boundaries. You must zoom in to see this layer.
Area Code Labels Shows labels for the U.S. area codes. You must zoom in to see this layer.
US Metro Boundaries (MSA) Marks U.S. Metropolitan Statistical Areas.
US Metro Labels (MSA) Shows labels for the U.S. Metropolitan Statistical Areas.
Place Names Displays the names of places from country names and borders to city names, bodies of water, parks, universities, and more. This layer is very dependent on the zoom level.
If you find that you have a set of layers that you’d always like to show whenever you create new workbooks or sheets, you can set the current selections as the default.
To set the current map options as the default:
1. Select the layers you want to show by default.
2. Select Update Defaults on the Map Options card menu.
Data Layers
In addition to map layers you can turn on a variety of predefined data layers that show census information. The data layers are added as shading to the map and a legend is shown to explain the colors. You can change the level to show as well as the color legend using the drop-down menus on the map options card.
Hide and show the legend by selecting View > Map Legend.
Washout
In addition to layers, you can use the Washout slider at the bottom of the Map Options card to control the intensity, or luminance, of the background map. The farther the slider moves to the right, the more faded the map will display behind your data.
Setting a Default Location
Sometimes your location names may exist in multiple countries and states. For example, Clark county exists in both Washington and Nevada. In those cases, Tableau does not have enough context to geocode the location. You can set your default location to tell Tableau which country and state your data refers to.
You can set your default location by selecting Data > Geocoding > Set Default Location. Then select the country and/or state that your data refers to.
If you have not set a default location Tableau maps the location name to the first matching location. A warning message shows in the status bar to indicate when this has happened.
iting Locations
Sometimes Tableau will not recognize one or more of the location names in your data. When this happens those values are marked as unrecognized and mapped to the (0,0) latitude and longitude coordinates. Often this happens because your data values may be spelled incorrectly or use an abbreviation that Tableau does not understand. You can edit the unrecognized location names to map to known locations by right-clicking the geographic field in the Data window and selecting Geographic Role > Edit Locations.
In the Edit Locations dialog box, select the correct location for each of the unrecognized values. For example, below Mass is unrecognized. Select Massachusetts to map it to the correct location.
Note:
Only unused locations are shown in the list, however, you can select the Show All Locations option at the bottom of the dialog box to select from all locations.
Custom Geocoding
By default Tableau recognizes a set of geographic roles that can be used to automatically geocode your data and create map views. For example, Tableau recognizes country names, state names, area codes, and so on. Refer to Geographic Roles for the complete list of built-in geographic roles. If your data contains geographic data that is not supported with the built-in roles you can use custom geocoding to plot the data on a map.
For example, let’s say your data contains Country, States, and Cities. The built-in geocoding hierarchy recognizes country and state so your data will be aggregated to that level. However you could import the latitude and longitude information so that Tableau can plot data at the city level too. Another example is to use custom geocoding to extend the existing geographic roles to include more values such as postal codes for the United Kingdom, France, Germany, and so on. Finally, you can use custom geocoding to add new geographic roles for airport codes, custom sales geographies, and more.
To set up custom geocoding you must first create and import a geocoding file that defines the hierarchies and geographical roles.
• Creating an Import File
• Importing Custom Geocoding
• Saving Custom Geocoding
Creating an Import File
When adding custom geocoding in Tableau, you need to import a file that contains the new geographic roles and their latitude/longitude definitions. These files should be in Comma Separated Values (CSV) format. Setting up the file differs depending on the type of custom geocoding you are adding. There are three general types of custom geocoding:
• Extending an Existing Role (e.g., adding postal codes for countries outside of the U.S.)
• Adding New Roles (e.g., adding airport codes under the existing Country level)
• Adding New Hierarchies (e.g., adding custom sales territories that are defined by a hierarchy of district, region, territory, and theater)
• Extending an Existing Role
• Adding New Roles
• Adding New Hierarchies
• Extending an Existing Role
• The built-in geocoding contains hierarchies that you can extend to include locations that are relevant to your data. For example, the existing hierarchy of Country > State only contains U.S. states. You can extend the state level to include states in other countries.The import file for this type of geocoding should contain all of the columns in every level of the hierarchy above the level you are extending. For example, the existing State level has both a Name and Abbreviation column. When adding new states make sure to include these two columns along with the parent column Country (Name). Below is an example.
•
•
•
• In the import file, the names of the columns define the Geographic Roles. That means when you want to extend the existing geocoding, you must make sure your column names match the existing geographic roles in the hierarchy that you are extending. That way the new locations will be added to the Country (Name), State (Name), and State (Abbreviation) roles.
• Save the file using a descriptive name such as UK States.
• Note:
• The highest level in the hierarchy is Country and cannot be extended to include higher levels such as Continent, etc.
• Adding New Roles
• Sometimes you may want to add entirely new geographic roles that still fit into the existing hierarchies in the built-in geocoding. For example, you may have data that contains Airport codes or National Park names that you want to plot on a map. These roles fit into the existing hierarchy of Country > State. The import file for this type of geocoding must contain the columns for each level in the existing hierarchy you are adding the role to. For example, airport codes are added to the Country > State hierarchy so the import file must contain all of the columns for the Country and State levels. Below is an example of an import file containing airport codes.
•
•
•
• Importing the file above would add the geographic roles Airport (ICAO), Airport (IATA), and Airport (City). Notice that again, the column name for country matches the existing Country (Name) geographic role.
• Name the file something descriptive such as Airports.
• dding New Hierarchies
• When you extend an existing role or add a new role you are working within the already existing hierarchies. However, you may have an entirely new hierarchy that you want to add. For example, you may have custom sales territories that are defined by Theater, Region, District, and Territory instead of Country, State, and Zip Code. This case is similar to creating new roles except there is no column mapping to an existing parent role. Instead you will need to create multiple import files, each representing a level in the new hierarchy.
• For this example, the top level in the hierarchy is theater so the import file would like like the example below.
•
•
•
• The next level is Region so its import file should contain columns for both Theater and Region. An example is shown below.
•
•
•
• Continue down the hierarchy making sure that each file contains columns for the current level and all of the levels above it. For example, the District file would contain columns for District, Region, and Theater.
• Save each import file into a single folder and name each file with the Level it represents. For this example there are four import files named Theater.csv, Region.csv, District.csv, and Territory.csv.
Importing Custom Geocoding
Once you have the import files set up you can import the custom geocoding into Tableau. Follow the steps below to import custom geocoding.
1. Select Data > Geocoding > Import Custom Geocoding.
2. In the Import Custom Geocoding dialog box, specify a directory that contains your import files. Refer to Creating an Import File to learn how to create an import file. You can use the button to the right of the text field to browse to the directory.
3. When finished, click Import.
The custom geocoding data is imported into the workbook and the new geographic roles become available. Refer to G eographic Roles to learn more about assigning roles and building map views.
Saving Custom Geocoding
When you import custom geocoding, the data is stored in a Local Data folder inside your repository. Your repository must be on a local drive in order for custom geocoding to work. Every time you import custom geocoding, all of the CSV files within the chosen directory are scanned and replace the geocoding that already imported. That is why you should store your import files in a single Custom Geocoding directory and always import from there. The custom geocoding becomes available for all workbooks you open from your machine.
When you save your workbook as a packaged wordbook, the custom geocoding data is packaged with the workbook.
Note:
When you open a packaged workbook you can import the custom into your own repository, but be careful because it will replace any other custom geocoding you may have imported.
You can remove the custom geocoding stored in your repository by selecting Data > Geocoding > Remove Custom Geocoding. Keep in mind that this will not remove the geocoding from a packaged workbook, it simply removes it from your repository.
Background Map Sources
Tableau comes with a set of online and offline maps that you can access to create map views. In addition, Tableau supports connecting to a Web Map Server (WMS) to support custom maps that are specific to your industry. You can specify the map source, import new maps sources, or export a map source to share with others.
To select a new map source:
• Select Data > Background Maps and then select the map source you want to use.
By default Tableau connects to an online map provider, offline map tiles that ship with the product, or you can add a WMS server.
• Working with WMS Servers
• Setting a Default Map Source
Working with WMS Servers
If you have a WMS server that provides custom maps that are specific to your industry you can add the server as a map source. After you added a WMS map server, you can export the map source to share with others or import a map source someone has shared with you.
To add a WMS server:
1. Select Data > Background Maps > WMS Servers
2. Click Add.
3. In the subsequent dialog box, type the URL for the server and click OK.
You can add as many map servers as you need. Each map source is displayed in the list of map sources on the Data menu. The map sources will be saved with the workbook and available to anyone you share the workbook with. You can also export the map source to a file that you can share with others so they can quickly connect to it.
To export a WMS server:
1. Select Data > Background Maps > WMS Servers.
2. Select the server you want to export as a map source and click Export.
3. Type a name for the file and choose a location. The file will be saved as a Tableau Map Source (.tms).
4. Click Save.
The Tableau Map source includes any default map options settings you have specified. For example, the map source will include any custom set of map layers you have specified to show by default. If you change the map options defaults you should export the map source again to include the new settings in the map source file.
To import a map source:
1. Select Data > Background Maps > WMS Servers.
2. Click Import.
3. Navigate to the saved map source file (.tms), select it, and click Open.
Setting a Default Map Source
By default all new worksheets connect to Tableau’s online map source. You can specify a different map source as the default.
To specify a default map source:
1. Select the map source on the Data > Background Maps menu.
2. Select Data > Background Maps > Set as Default.
Map Storing and Working Offline
When you create map views using the online map provider, Tableau stores the images that make up the map in a cache. That way, as you continue your analysis you don’t have to wait for the maps to be retrieved. In addition, by storing the maps you can do a certain amount of work when you are offline.
The cache for the maps are stored with your Internet Explorer Temporary Internet Files and can be cleared at any time simply by deleting the temporary files in Internet Explorer.
When you are working offline and open a view that uses a map that is stored in the cache, the stored image will be used automatically. However, there are several actions that require Tableau to retrieve a new map. If the new map is not also stored in the cache you won’t be able to load the map until you go online. The main actions to be aware of are listed below.
• Turning layers on or off – if you decide to turn on a layer that isn’t stored in the cache, Tableau will need to connect to retrieve the necessary information.
• Zooming – zooming in or out on a map requires different map images. If the images at the specified zoom level don’t exist in the cache, Tableau will need to retrieve the updated maps.
• Panning – panning sometimes requires new map images. If you are working offline and don’t have the necessary map images and legends stored in the cache, the new images and legends will not load.
Stored map images and legends remain valid for about thirty days. After that time, Tableau will not use the stored image, instead it will require you to reconnect and fetch an updated map. This is to prevent the map images from becoming outdated.
Trend Lines and Statistics
You can use Tableau’s trend line feature to incrementally construct interactive models of behavior that you can use to make predictions about your data. For example, answer questions like whether profit is predicted by time, or whether average delays at an airport are significantly correlated with the month of the year.
• Adding Trend Lines
• Remove Trend Lines
• The Trend Line Model
• Removing Factors from the Model
• Testing Significance
• Trend Lines Example
• Assumptions
• Trend Line Model Terms
• Commonly Asked Questions
Adding Trend Lines
When you add trend lines to the view, you can specify several options about how you want them to look and behave.
• Add Trend Lines to the View
• Why can’t I add Trend Lines?
Add Trend Lines to the View
1. Select Analysis > Trend Lines or right-click on the pane and select Trend Lines.
This command adds a linear trend line and will add a trend line for each color if there is a discrete field on the color shelf. You can optionally continue with the steps below to specify different trend line options.
2. Right-click on the pane and select Edit Trend Lines to open a dialog box where you can specify the following options:
o Select either a Linear, Logarithmic, or Polynomial model type.
o Select fields you want to include as factors in the trend line model. For example, on a view of yearly sales for three different products, you may want to see the overall sales trend across all products rather than a different line for each product. In this case, you would exclude the product field as a factor. As shown here:
For more information on excluding factors refer to Removing Factors from the Model.
o Select whether to exclude color. When you have color encodings in your view, you can use this option to add a single trend line that models all of the data ignoring the encoding.
o Select whether to force the intercept to zero. This option is useful when you know that you want your trend line to begin at zero. For example, you may have an analysis of the number of products sold over time. You know that you started out with zero products sold, so you can force the trend line to begin there.
3. When finished click OK.
4. Why can’t I add Trend Lines?
5. To add trend lines to a view, both axes must contain a field that can be interpreted as a number. For example, you cannot add a trend line to a view that has a Product Category file, that contains strings, on the columns shelf and a profit measure on the rows shelf. However, you can add a trend line to a view of sales over time because both sales and time can be interpreted as numeric values.
6. If you have trend lines turned on and you modify the view in a way where trend lines are not allowed, the trend lines will not show. When you change the view back to a state that allows trend lines, they will reappear.
7. Note:
8. Tableau automatically stacks bar marks in many cases. However, trend lines cannot be turned on for stacked bars. You can turn off stacked marks by selecting Off on the Analysis > Stacked Marks menu.
Remove Trend Lines
To remove trend lines from the view, simply select Analysis > Trend Lines or right-click the pane and select Trend Lines. The Trend Lines command toggles between on and off. If you have specified any trend line options, they will be used the next time you turn on trend lines. However, if you close the workbook with trend lines turned off, the trend line options will be reset to the default settings.
The Trend Line Model
Any time you add a trend line to your view you are building a statistical model. You are answering the question of whether the factors in your view predict a specific value (measure). A simple example is to wonder whether profit is predicted by time in a view that shows the profit of a company over four years.
When you add a trend line in Tableau, the line you see is defined by a mathematical formula of the form: Y = factor 1 * factor 2 * …factorN * f(x) + random error
The term Y in the above expression is called the response variable and corresponds to the value you are trying to predict while the term X is the independent variable. The factors in the expression correspond to the categorical fields in the view (time in the profit example above). In addition, each factor is represented as a matrix. The * is a particular kind of matrix multiplication operator that takes two matrices with the same number of rows and returns a new matrix with the same number of rows. That means that in the expression factor 1 * factor 2, all combinations of the members of factor 1 and factor 2 will be introduced. For example, if factor 1 and factor 2 both had three members, then a total of nine variables will be introduced into the model formula by this operator.
Removing Factors from the Model
You can remove factors from the model using the Trend Lines Options dialog box. Often you will want to remove factors because you want the trend line model to be based on the entire row in the table rather than broken up by the members or values of a field. Consider the following example. The view below shows the monthly sales for two different products, broken down by region.
When you select a trend line in the view, you can see that a separate model is created for each customer segment.
Now remove Market as a factor in the model by deselecting it in the Trend Lines Options dialog box.
You can see that the trend line model is now based on all rows associated with the Product Type field. That is, the trend line depends only on the product type and is the same in each row of a given column.
Testing Significance
Once you’ve added a trend line to the view, you typically want to know the goodness of fit of the model, which is a measure of the certainty of the model’s predictions. In addition, you may be interested in the significance of each factor. You can view these numbers using the Describe command.
In the Sheet Description dialog box, there is a section of statistics about the Trend Line Model. When you are testing the significance, you are most concerned with the p-values. The smaller the p-value, the more significant the model or factor is. It is possible to have a model that has statistical significance but a factor that not really adding much to the model.
• Entire Model Significance
• Significance of Specific Fields
• Significance of Individual Trend Lines
• Entire Model Significance
• To identify the significance of the entire trend line model, select Analysis > Describe Trend Line Model. In the Analysis of Variance table, the p-value is listed. The smaller the p-value, the more significant the model is.
•
•
•
• This p-value is a comparison of the fit of the entire model to the fit of a model composed solely of the grand mean (the average of data in the data view). That is, this p-value assesses the explanatory power of the quantitative term f(x) in the model formula, which can be linear, polynomial or logarithmic with the factors fixed. It is common to test significance by applying the “95% confidence” rule. This equates to a p-value of 0.05 or less.
• Significance of Specific Fields
• To identify the significance of specific fields in a trend line model, select Analysis > Describe Trend Line Model. In the Analysis of Variance table, which is often referred to as an ANOVA table, each of the fields that are factors in the model are listed. For each field, among other values, you can see the p-value. The p-value indicates how much that field adds to the significance of the entire model. The smaller the p-value the more that field improves the model. The values displayed for each field are derived by comparing the entire model to a model that does not include the field in question.
• The following image shows the ANOVA table for a view of quarterly sales for the past two years of three different product categories.
•
•
•
• As you can see, the p-value for Product Category 1 is smaller than the p-value for Order Date. That leads us to believe that the product category is more statistically significant in predicting the total sales than the year. However, both of these factors are statistically significant in this model. (It is common to test significance by applying the “95% confidence” rule. This equates to a p-value of 0.05 or less.)
Significance of Individual Trend Lines
You can view the p-value and formula for individual trend lines in two ways. Either right-click a specific trend line in the view and select Describe Trend Line or select Analysis > Describe Trend Line Model and see the Individual Trend Line table.
The Individual Trend Line table in the Describe Trend Line Model dialog box lists of all the trend lines in your view along with their p-value and the formula. This is an easy way to quickly determine which (if any) trend lines are statistically significant.
Trend Lines Example
This example walks through the steps of incrementally testing the significance of various factors to solve a problem.
We start with the question: what is causing high discount rates at a superstore? While statistics cannot answer this question, we can discover the variables that are correlated with high discounts. The view below shows the average monthly discount rates of all stores between 2002 and 2004. Refer to A dding Trend Lines to learn how to add and remove trend lines.
Our first thought is that the discount rates may be high on out of stock items and employees are offering discounts for the inconvenience. When we put the Product Inventory field on the rows shelf, we see that there was a large spike in discount rates for out of stock items in 2002 and other increases in 2004.
However, when looking at the ANOVA table for the trend line model, we see that the p-value for product inventory is 0.0671058. For most, this number is too high to be significant, indicating that we cannot predict discount rates based on inventory. (It is common to test significance by applying the “95% confidence” rule. This equates to a p-value of 0.05 or less.)
Still on the search to find out what is causing the high discount rates, we decide that it could be based on product categories. Certain categories may have higher discounts applied. It seems plausible so we put Product Category 3 on the rows shelf.
When we open the Describe Trend Line Model dialog box, and look at the ANOVA table this time, we see that the p-value for product category 0.0001 and the p-value for the entire model is less than 0.0001. With that, we can be confident that the changes in average discount rates over time are in fact quite different for different products. In other words we can be statistically confident that the different trend lines slopes for different products isn’t just due to randomness, but rather there is a real correlation between a product and the slope of this line.
In addition to product category 3, we notice that the year field offers a statistically significant improvement to the model. Refer to Entire Model Significance for more information.
Assumptions
The p-values reported in Tableau trend lines depend on some assumptions about the data. The first assumption is that, whenever a test is performed the model for the mean is (at least approximately) correct.
The second assumption is that the “random errors” referred to in the model formula (refer to The Trend Line Model) are independent across different observations and that they all have the same distribution. This would be violated if the response variable had much more variability around the true trend line in one category vs. another. For instance, in the example shown in this chapter, this would be violated if the discount rate had much more variability around the true trend line in one market vs. another.
Trend Line Model Terms
When you describe the trend line model, there are several values listed. This section discusses what each of these values means.
• Model Formula
• Number of Observations
• Residual DF (residual degrees of freedom)
• DF (degrees of freedom)
• SSE (sum squared error)
• MSE (mean squared error)
• R-Squared
• Standard error
• P (significance)
• Analysis of Variance
• Individual trend lines
Model Formula
This is the formula for the full trend line model. The formula reflects whether you have specified to exclude factors from the model.
Number of Observations
This is the number of rows used in the view.
Residual DF (residual degrees of freedom)
For a fixed model, this value is defined as the number of observations minus the number of parameters estimated in the model
DF (degrees of freedom)
In the ANOVA table, this column refers to the degrees of freedom of the smaller model in the given row.
SSE (sum squared error)
The term SSE generally refers to a “sum of squared errors.” The errors are the difference between the observed value and the value predicted by the model. In the ANOVA table, this column is actually the difference between the SSE of the simpler model in that particular row and the full model, which uses all the factors. This SSE also corresponds to the sum of the differences squared of the predicted values from the smaller model and the full model.
MSE (mean squared error)
The term MSE refers to “mean squared error” which is the SSE quantity divided by its corresponding degrees of freedom.
R-Squared
A measure of the percent of the variability explained by the full model as compared to a smaller model.
If SSE(F) is the sum of squared errors of the full model, and SSE(S) the sum of squared errors from the smaller model, then
R^2 = (SSE(S)-SSE(F))/SSE(S)
measures the relative drop in variability of the response when using the full model as opposed to the smaller model. In the Describe Trend Line Model dialog box, this model is the model that has a different mean for each pane. This is different than the most common use of R-squared, which compares the model to a model with just a grand mean.
Standard error
The square root of the MSE of the full model. It is an estimate of the standard deviation (variability) of the “random errors” in the model formula.
P (significance)
The probability that an F random variable with the above degrees of freedom exceeds the observed F in this row of the ANOVA table.
Analysis of Variance
This table, also known as the ANOVA table, lists information for each factor in the trend line model. The values are a comparison of the model without the factor in question to the entire model, which includes all factors.
Individual trend lines
This table provides information about each trend line in the view. For each trend line, you can see its p-value as well as its equation. This is helpful when you have a lot of panes in the view, each with its own trend line. Looking at the list you can see which, if any, are the most statistically significant
Commonly Asked Questions
This section describes some commonly asked questions regarding trend lines in Tableau.
How do I change the confidence level used in the model?
Tableau does not enforce a confidence level. It simply reports the significance of the whole model, or of a specific field, by showing the p-value. A p-value of 0.05, for instance, implies that you can be 95% sure of the model. A p-value of 0.10 means that you can be 90% confident of the model’s prediction. So read the reported p-values, and then use whatever rules you wish to determine statistical significance.
What does it mean if the p-value for the model is significant but the p-value for the specific field in the ANOVA table is not significant?
The p-value in the ANOVA table indicates whether the field adds or detracts from the significance of the entire model. The smaller the p-value the more the field improves the model. The values displayed for each field are derived by comparing the entire model to a model that does not include the field in question. So, this means that the model is statistically significant, but that you cannot be confident that the specific field in question adds anything to it. One thing to inspect in a case like this is whether you are better off removing the factor from the model.
What does it mean if the p-value for the model is not significant but the p-value for the specific field in the ANOVA table is significant?
This could happen in a case when there is no “trend” within each pane. For example, the lines are flat, but the mean varies across a given factor.
Log Axes
Sometimes you will have a measure that uses a logarithmic scale as opposed to linear. For example, some well known logarithmic scales include the Richter magnitude scale to measure the strength of earthquakes, pH to measure acidity, and the stellar magnitude scale, which measures the brightness of stars. You can Edit the axis scale for any measure to be logarithmic using the Edit Axis dialog box. By default the tick marks are drawn at powers of ten, however, you can specify any base that is greater than 1.
To change the scale of an axis:
1. Right-click the axis in the view and select Edit Axis.
2. In the Edit Axis dialog box, select Logarithmic for the axis scale.
3. Select the Tick Marks tab.
4. Select one of the following Major Tick mark options:
o Automatic – the major tick marks are drawn at powers of 10.
o Fixed – the major tick marks are drawn at a specified exponent. Type a number into the Powers of text box.
o None – major tick marks are not shown.
5. When finished, click OK.
You can also reverse the axis by selecting Reverse in the Scale area on the General tab of the Edit Axis dialog box.
Note:
If your data contains negative values Tableau cannot plot them on a logarithmic scale. All values with a negative value will be displayed at 1 on the axis. You can then filter these records to exclude them from the view
Easy Tableau 