KNOWLEDGE BASE

Error "Can't compare {type} and {type} values" in Calculation Editor


Published: 27 Sep 2019
Last Modified Date: 30 Sep 2019

Issue

When writing a condition in a calculation, then the calculated field is invalid with an error similar to

Can't compare {type} and {type} values

For example: 
Can't compare date and string values
or,
Can't compare datetime and string values
 
The error will occur with these combinations of data types:
 StringFloatIntegerBooleanDate/Datetime
String XXXX
FloatX  XX
IntegerX  XX
BooleanXXX X
Date/DatetimeXXXX 

Environment

Tableau Desktop

Resolution

Only compare values of the same type. Depending the calculation this may mean that using a different field or literal value, or converting one of the fields or literal values. The "can't compare example.twbx" workbook in the right-hand pane of this article provides some examples.
  • [Date Field] = "1/1/2019" could become [Date Field] = #1/1/2019#
  • [Text Field with Only Number Values] = [Number Field] could become FLOAT([Text Field with Only Number Values] ) = [Number Field]
  • [Mixed Text Field] = [Number Field] could become [Mixed Text Field] = STR([Number Field])

Cause

Fields and/or literal values must have the same data type to be compared.

Additional Information

Fields with a geographic role also have a data type. The data type, rather than the geographic role, is what must match.

Sets always return Boolean values.

Hashtags are used to define a date literal, such as #1/1/2019#. Quotations are used to define a string literal, such as "text value". Numeric literals do not need special characters, such as 4 or 4.0. Similarly Boolean literals are just the keywords true and false (capitalization does not matter).
Did this article resolve the issue?