Type conversion functions in Tableau - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to prepare a report that shows total sales by month and also the average sales per customer. However, the sales data has some numbers stored as text, and the dates are stored as strings. You need to convert these to the correct types to perform calculations.

📊 Data: You have a sales dataset with columns: 'Order ID' (text), 'Order Date' (string in format 'YYYY-MM-DD'), 'Customer ID' (text), 'Sales Amount' (text representing numbers), and 'Quantity' (number).

🎯 Deliverable: Create a Tableau worksheet that shows total sales by month and average sales per customer, using type conversion functions to fix data types.

Sample Data

Order ID	Order Date	Customer ID	Sales Amount	Quantity
ORD001	2024-01-15	CUST01	100.50	2
ORD002	2024-01-20	CUST02	200	1
ORD003	2024-02-05	CUST01	150.75	3
ORD004	2024-02-18	CUST03	300	1
ORD005	2024-03-10	CUST02	250.25	2
ORD006	2024-03-15	CUST04	400	4
ORD007	2024-03-20	CUST01	350	1
ORD008	2024-04-01	CUST03	500.50	5

1

Step 1: Create a calculated field named 'Sales Amount Number' to convert 'Sales Amount' from text to number.

FLOAT([Sales Amount])

Expected Result

Converts sales amounts like '100.50' and '200' to numeric values 100.5 and 200.

2

Step 2: Create a calculated field named 'Order Date Date' to convert 'Order Date' from string to date type.

DATE([Order Date])

Expected Result

Converts strings like '2024-01-15' to date values January 15, 2024.

3

Step 3: Create a calculated field named 'Month' to extract the month and year from 'Order Date Date' for grouping.

DATETRUNC('month', [Order Date Date])

Expected Result

Groups dates like '2024-01-15' and '2024-01-20' into January 2024.

4

Step 4: Create a worksheet with Rows set to 'Month' and Columns showing 'SUM of Sales Amount Number'.

Drag 'Month' to Rows, drag 'Sales Amount Number' to Columns, set aggregation to SUM.

Expected Result

Shows total sales for each month: Jan, Feb, Mar, Apr.

5

Step 5: Create a calculated field named 'Average Sales per Customer' to calculate average sales per unique customer.

SUM([Sales Amount Number]) / COUNTD([Customer ID])

Expected Result

Calculates average sales amount per customer across all orders.

6

Step 6: Add 'Average Sales per Customer' as a label or tooltip on the monthly sales chart for additional insight.

Drag 'Average Sales per Customer' to Tooltip or Label shelf.

Expected Result

Shows average sales per customer for each month when hovering or as label.

Final Result

Month       | Total Sales | Avg Sales per Customer
-----------------------------------------------
2024-01-01  | $300.50     | $150.25
2024-02-01  | $450.75     | $225.38
2024-03-01  | $1000.25    | $333.42
2024-04-01  | $500.50     | $500.50

✓Sales increased steadily from January to March.

✓March had the highest total sales.

✓April had the highest average sales per customer but only one order.

Bonus Challenge

Create a calculated field that converts 'Quantity' to text and then back to number, and verify it matches the original 'Quantity'.

Show Hint

Use STR() to convert number to string and INT() or FLOAT() to convert back to number.

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