Two common errors when using expressions

Users new to expressions in Analytics often make two common errors:

Creating an invalid mix of data type and operation, resulting in an “Expression type mismatch” error message
Creating a character, numeric, or datetime expression in a situation or location in which only a logical expression is valid, resulting in an “Expression type invalid” or “Logical expression is required” error message

“Expression type mismatch”

In order for an expression to be valid, the data type of the values in the expression must match the calculation or operation you are performing. For example, you cannot multiply two character fields, or divide two date fields. If you try, an “Expression type mismatch” error message is displayed and no processing occurs.

Any of the following methods can allow you to avoid this error, although not all of them may be an appropriate solution for your particular situation:

Keep the values the same, but change the operation

For example, you cannot add two time fields, so Finish_Time + Start_Time is not valid. However, you can subtract one time field from another, so Finish_Time - Start_Time is valid.
Keep the operation the same, but change one or more of the values

For example, you cannot subtract a number from a character value, so DATE( ) - 2 is not valid, because the DATE( ) function without any parameters returns the current operating system date as a character value. However, you can subtract a number from a date value, so TODAY( ) - 2 is valid because the TODAY( ) function returns the current operating system date as a date value.
Keep the operation the same, but change the data type of one or more of the values

For example, you cannot compare a date to a number, so Finish_Date > 20141231 is not valid. However, you can compare a date to a date, so Finish_Date > `20141231` is valid. The addition of the backquotes (`) changes the numeric literal to a date literal.

In some cases, you can use functions to convert the values or fields in an expression to a data type appropriate for the expression. For more information about conversion functions, see Harmonizing fields.

“Expression type invalid”, or “Logical expression is required”

You must ensure that the return value of an expression is the correct data type for the situation. In many areas of the application where an expression is required, the return value must be a particular data type. If it is not the required data type, an “Expression type invalid” or “Logical expression is required” error message is displayed and no processing occurs. Even if an expression is valid, Analytics displays an error message if the expression returns the wrong data type for the situation.

For example, the expression Quantity_on_Hand * Unit_Cost could be generally valid, and work as expected, providing a numeric total, if you use it to create a computed field. However, the same expression would return an error if you used it when creating a view filter or an IF statement, which require that expressions return a logical value of True or False (T or F). If you altered the expression to Quantity_on_Hand * Unit_Cost > 5000 then it would work correctly for a filter or an IF statement.

The key point to be aware of is the location from which you open the Expression Builder. Clicking Edit View Filter beside the Filter text box, or clicking the If button in various locations, requires the creation of a logical expression in the Expression Builder.

[ Back to top ]