Data types play an essential role in building dashboards with Tableau. Data types determine how formulas and visualizations interpret data in a column or even within parts of a formula. Mismatched data types will cause errors in Tableau preventing you from creating necessary calculations and visualizations. We’ll explain what data types are, how to identify them, how to permanently change them and how to change them temporarily within calculated fields.
If you’re new to working with data types, it’s a common problem across different business intelligence tools. If you’ve ever seen an Excel spreadsheet with a green arrow in a cell asking to convert to number, you may have even experienced changing data types without even knowing.
Let’s take a look at what data types are and how to work with them!
Table of Contents
Understanding Data Types in Tableau
Before diving into the specifics, it’s crucial to understand why data types are and why they are important, in Tableau and in data transformations in general. The concept is widely used across a number of business systems and comes up often when working with data.
Data Types come in 3 basic types. Text (also referred to as a string), Numbers, and Dates. Depending on the system, you may have additional options that define things like the number of characters in a text field, the number of decimal places in a number, and whether or not a date field presents date and time, or just the date etc.
When a column is defined as each one of these types, Tableau will treat it differently.
- Adding two number columns together results in the sum.
- Adding two text columns together results in a concatenated sentence.
- Adding a number and text column together results in an error message.
The contents of columns may look identical when you view them. It’s the data type that tells Tableau how to process the data or interpret the applied formulas.
Data Types Available in Tableau
Tableau offers several different data types when you’re assigning them to fields. The primary data types you will use on a daily basis are as follows:
- String – Represents textual data.
- Number (Whole) – Contains integer values without decimal points.
- Number (Decimal) – Holds numeric values with decimal points.
- Date – Represents dates in calendar format.
- Date & Time – Combines date and time information.
- Boolean – Stores true or false values.
- Geographic – Contains geographical data like countries, cities.
Geographic data types include geospatial coordinates, along with specific types for cities, states, countries, zip codes etc. which are used specifically for mapping coordinates and key points.
How to Identify Data Types in Tableau
Tableau data types can be identified by the icons present on the Data Source view or from the Worksheet View. They are notated by small icons either to the top of each column in the data source view, or the left of a field name in the worksheet view. Additional detail can be found under the Table Details tab of the Data Source view.
Here are some examples of data types being displayed in each view.
Data types from the data source view
The data source view is where you build your data model, import data and create connections. At the bottom of the tab, you will see a preview of your data. The top of each column has a column header with an icon which represents whether a field is numeric, a date, text, boolean, etc.
Data types displayed on the worksheet view
In the worksheet view, the data type icons are presented to the left of each column or field name.
Example of Data Types with icons and detail from Table Details View
On the left side of the data sources tab, there is a Table Details panel that is usually collapsed by default. If you expand the Table Details panel, it will show you the exact data type. Numeric fields could be whole numbers or decimals but use the same Tableau data type icon to represent them as a number.
Tableau tries to automatically detect the data type of the incoming data, but it is not always correct. There are times that you want to override the default data type that comes in. Let’s look at how to change Tableau data types.
Changing Data Types in Tableau
To change data types in Tableau, you can permanently change the data type of a column by navigating to the Data Source view, click on the data type icon at the top of a column, and select a new data type from the drop down menu. Data types can be temporarily updated in a calculated field by applying formulas.
We’ll look at both methods.
Updating Tableau Data Types from the Data Sources tab
To change the data type in Tableau, navigate to the data sources tab. Click the data type icon at the top of a column, click on the icon to see a contextual menu of potential data types. Select a data type from the list and it will update the data type for the column.
Changes made in the data sources tab will be applied to all other formulas and field calculations in your Tableau dashboard. Be sure to double check your calculations to make sure that the data type changes do not affect any calculations you’ve previously created.
Using Formulas to Change Tableau Data Types
When creating new calculated fields there are times that you need to temporarily adjust the data type that Tableau uses to evaluate the column. For example, if you want to concatenate a numeric field with a string field but don’t want to permanently change your numeric field to a string (text).
Here’s how to convert different data types using Tableau formulas.
Converting a Field to Text
The STR function converts a field of any type to text. This is particularly useful when dealing with numerical or date values that you want to display as part of a string.
Formula: STR([FieldName])
Converting a Field to a Number
The INT function converts a field to an integer. It’s ideal for fields where decimal precision isn’t required. Use FLOAT when dealing with numbers that require decimal points. It converts the field into a floating-point number.
Formula for Whole Numbers: INT([FieldName])
Formula for Decimal Numbers: FLOAT([FieldName])
Converting a Field to a Date Value
The DATE function is used to convert a string or a number into a date format. This is essential when you are working with dates stored as strings or timestamps.
Formula: DATE([FieldName])
By using these data type modifications you can quickly adjust the data type for a single use in a formula to avoid having to do a permanent update. The starting type of the data doesn’t matter, but converting between text and numbers are by far the most common. For date conversions, the text will need to be formatted in the standard Tableau date format for it to be appropriately evaluated.
Troubleshooting Tableau Incorrect Sorting with Data Types
If Tableau is incorrectly sorting numbers, dates, or text make sure to check the field’s data type. To sort appropriately, numbers must be defined with a numeric data type, and dates should be defined as a date data type instead of text.
Here are some common issues that arise:
Alphanumeric Data – If a field that should be text (string) is incorrectly set as a number, alphanumeric sorting won’t work correctly. For example, ’10’ will appear before ‘2’ if sorted as a string, but after ‘2’ if sorted as a number.
Numeric Data – When numeric data is incorrectly formatted as a string, numerical sorting may yield unexpected results. Instead of sorting based on numerical value (1, 2, 10), Tableau will sort them alphabetically (1, 10, 2).
Date Data – If date fields are incorrectly formatted as strings or numbers, they won’t be sorted chronologically. Instead, they’ll be sorted based on their string or numeric order, which can significantly distort temporal analyses.
Data type mismatches are one of the most common errors when working in Tableau, but they’re luckily one of the quickest errors to fix. Switch to the data source view and update the data type or try using a formula to adjust the data type in a field calculation.
Conclusion
Working with data types is a fundamental aspect of working with Tableau and is directly relevant to other popular reporting and database tools, such as Excel, Power BI, and SQL. When a data type isn’t appropriately assigned Tableau is good about giving you an error message letting you know that the calculation cannot be completed, and a change needs to be made.
It’s a good practice to review data types when you first import data into Tableau, while the software will automatically assign data types it is not always accurate and can lead to some confusing and unexpected results when you add fields to a visual.