Type conversion functions in Tableau - Dashboard Guide

Dashboard Mode - Type conversion functions

Business Question

How can we convert data types in Tableau to analyze sales data correctly and display meaningful KPIs and charts?

Sample Data

Order ID	Customer	Order Date	Sales (Text)	Quantity (Text)	Region
1001	Alice	2024-01-15	"100.50"	"2"	East
1002	Bob	2024-02-20	"200"	"3"	West
1003	Charlie	2024-03-05	"150.75"	"1"	East
1004	Diana	2024-04-10	"300"	"4"	South
1005	Eva	2024-05-18	"250.25"	"2"	West

Dashboard Components

• KPI Card: Total Sales
  Formula: FLOAT([Sales (Text)]) converted and summed
  Calculation: SUM(FLOAT([Sales (Text)])) = 100.50 + 200 + 150.75 + 300 + 250.25 = 1001.5
  Shows total sales as a number
• KPI Card: Total Quantity
  Formula: INT([Quantity (Text)]) converted and summed
  Calculation: SUM(INT([Quantity (Text)])) = 2 + 3 + 1 + 4 + 2 = 12
  Shows total quantity sold
• Bar Chart: Sales by Region
  Uses FLOAT([Sales (Text)]) for numeric sales values
  Aggregates sales per region:
  East: 100.50 + 150.75 = 251.25
  West: 200 + 250.25 = 450.25
  South: 300
  Shows bars representing these totals
• Table: Orders with Converted Types
  Columns:
  - Order ID
  - Customer
  - Order Date (Date type)
  - Sales (converted to number)
  - Quantity (converted to number)
  - Region
  Shows raw and converted data side by side

Dashboard Layout

+----------------------+----------------------+
|   Total Sales KPI    |  Total Quantity KPI  |
+----------------------+----------------------+
|                      Bar Chart: Sales by Region                      |
+---------------------------------------------------------------------+
|                      Table: Orders with Converted Types             |
+---------------------------------------------------------------------+

Interactivity

A filter on Region allows users to select one or more regions. When a region is selected:

• The Total Sales and Total Quantity KPIs update to show sums only for the selected regions.
• The Bar Chart updates to show sales bars only for the selected regions.
• The Orders Table filters to show only orders from the selected regions.

This helps users focus on specific areas and see how sales and quantities change.

Self Check

If you add a filter for Region = East, which components update and what are their new values?

• Total Sales KPI: 100.50 + 150.75 = 251.25
• Total Quantity KPI: 2 + 1 = 3
• Bar Chart: Shows only one bar for East with value 251.25
• Orders Table: Shows only orders 1001 and 1003

Key Result

Dashboard showing total sales and quantity with type conversion, sales by region bar chart, and detailed orders table.

Practice

1. Which Tableau function converts a string like '123' into an integer?

easy

A. FLOAT()

B. STR()

C. DATE()

D. INT()

Solution

1. Step 1: Understand the input type

   The input is a string '123' which represents a number but is stored as text.

2. Step 2: Choose the function to convert string to integer

   INT() converts a string that looks like a number into an integer type.

3. Final Answer:

   INT() -> Option D

4. Quick Check:

   String '123' to number = INT() [OK]

Hint: Use INT() to convert numeric strings to whole numbers [OK]

Common Mistakes:

• Using STR() which converts numbers to strings
• Using DATE() which converts to date format
• Using FLOAT() when integer is needed

2. Which of the following is the correct syntax to convert a date string '2023-06-01' to a date in Tableau?

easy

A. FLOAT('2023-06-01')

B. DATE('2023-06-01')

C. INT('2023-06-01')

D. STR('2023-06-01')

Solution

1. Step 1: Identify the data type to convert to

   The string '2023-06-01' represents a date, so we want to convert it to a date type.

2. Step 2: Use the DATE() function for conversion

   DATE() converts a string formatted as a date into a Tableau date type.

3. Final Answer:

   DATE('2023-06-01') -> Option B

4. Quick Check:

   Convert date string to date = DATE() [OK]

Hint: Use DATE() to convert date strings to date type [OK]

Common Mistakes:

• Using STR() which converts to string, not date
• Using INT() or FLOAT() which cause errors on date strings
• Missing parentheses in function call

3. What is the result of this Tableau calculation?

INT('45.67')

medium

A. Error

B. 45

C. 46

D. 45.67

Solution

1. Step 1: Analyze the input string

   The string '45.67' represents a decimal number.

2. Step 2: Understand INT() behavior on decimal strings

   INT() expects a string representing an integer; passing a decimal string causes an error in Tableau.

3. Final Answer:

   Error -> Option A

4. Quick Check:

   INT('45.67') causes error [OK]

Hint: INT() only converts strings representing whole numbers [OK]

Common Mistakes:

• Expecting an error on decimal strings
• Thinking INT() rounds up to 46
• Confusing truncation with keeping decimals

4. You wrote this Tableau formula but get an error:

FLOAT('abc')

What is the likely cause?

medium

A. The string 'abc' cannot convert to a number

B. FLOAT() requires a date input

C. Missing quotation marks around abc

D. FLOAT() only works on integers

Solution

1. Step 1: Check the input string

   The string 'abc' contains letters, not numeric characters.

2. Step 2: Understand FLOAT() conversion rules

   FLOAT() converts strings representing numbers to decimal numbers; non-numeric strings cause errors.

3. Final Answer:

   The string 'abc' cannot convert to a number -> Option A

4. Quick Check:

   Non-numeric string to FLOAT() = Error [OK]

Hint: Only numeric strings convert with FLOAT() [OK]

Common Mistakes:

• Thinking FLOAT() works on any string
• Assuming FLOAT() needs date input
• Forgetting quotes around strings

5. You have a field [Sales] stored as string values like '1000', '2000', and '3000'. You want to calculate the average sales as a number. Which formula correctly converts and averages these values?

hard

A. INT(AVG([Sales]))

B. AVG(STR([Sales]))

C. AVG(INT([Sales]))

D. AVG(DATE([Sales]))

Solution

1. Step 1: Convert string sales to numbers before averaging

   Since [Sales] is string, convert each value to integer using INT() first.

2. Step 2: Apply AVG() on converted integers

   AVG(INT([Sales])) calculates the average of numeric sales correctly.

3. Final Answer:

   AVG(INT([Sales])) -> Option C

4. Quick Check:

   Convert then average = AVG(INT()) [OK]

Hint: Convert strings to numbers before aggregation [OK]

Common Mistakes:

• Averaging strings directly causes errors
• Converting after averaging strings is invalid
• Using DATE() on numeric strings