Type conversion functions in Tableau - Cell-by-Cell Formula Trace

Sample Data

Sample data with text strings representing different data types: integer, decimal, boolean, date, and text.

Cell	Value
A1	123
A2	45.67
A3	TRUE
A4	2024-06-01
A5	Hello

Formula Trace

INT([A2])

Step 1: Value in A2 is '45.67' (string)

Step 2: INT('45.67')

Cell Reference Map

   A   
1 123  
2 45.67
3 TRUE 
4 2024-06-01
5 Hello

The formula references cell A2 which contains the string '45.67'.

Result

   A    B
1 123   
2 45.67 45
3 TRUE   
4 2024-06-01
5 Hello  

The result of INT([A2]) is 45, shown in cell B2 next to the original value.

Sheet Trace Quiz - 3 Questions

What does INT('45.67') return in Tableau?

A 46

B 45

C 45.67

D Error

Key Result

INT(string_number) converts a string representing a number to an integer by truncating decimals.

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