Troubleshooting property expressions

Note: Some of the functionality described here can only be authored or accessed using the installed Spotfire client. You can write all of the expressions mentioned in the web client, but the installed client has some shortcuts and interfaces that are not available in the browser.

The following messages can be encountered when inserting properties in the Expression field of the Custom expression dialog or the Add calculated column dialog.

Some of the problems can also occur if you add a property to an axis using Set from Property and the expression does not match the axis. In that case, you must right-click and go to the Custom expression dialog to manually change your expression. Try to identify your problem using the table below.


Error text for the expression field	Expression example	Possible errors	Solution
The expression is not complete. or The expression is empty. or The expression cannot be evaluated.	`${MyProperty}`	When a property is inserted to an expression using double-click or by clicking on the Insert Properties button in the installed client, it is inserted as text. With this syntax, Spotfire will try to interpret a string property value as a column name or a part of an expression rather than as a value.	If you want to use the value of the string property, put quotation marks around the expression: `"${MyProperty}"` You can also right-click on the property in the Available properties for column list (in the installed client) and select Insert as Value from the pop-up menu instead: `DocumentProperty("MyProperty")` If the property is supposed to hold a column name, but the column name contains space characters, you need to put "[" and "]" characters around the property expression. You can also use the $esc() function that both escapes any "]" characters and converts the property string to a column: `$esc(${MyProperty})` If the property is added using Set from Property, you will automatically get the escaped version of the expression.
The expression is not complete.	`Concatenate("My first string", ${EmptyProperty})`	If a string property inserted as text is empty then it cannot be interpreted as a column and Spotfire will not see that the second argument in this example is there at all.	If you want to use the value of the string property, put quotation marks around the expression: `Concatenate("My first string", "${EmptyProperty}")` You can also right-click on the property in the Available properties for column list (in the installed client) and select Insert as Value from the pop-up menu instead: `Concatenate("My first string", DocumentProperty("EmptyProperty"))` If you want to use the content of a column in the concatenation, you should put '[' and ']' characters around the property to make sure the property is interpreted as a column (or use `$esc()` as described above). `Concatenate("My first string", [${EmptyProperty}])`
Invalid type for function call 'DocumentProperty'	`DocumentProperty(MyProperty)`	The property name should always be written within quotation marks when property functions are applied. Quotation marks are automatically added if you use the Insert as Value shortcut from the pop-up menu (in the installed client).	Put quotation marks around the property name: `DocumentProperty("MyProperty")`
Expected 'End of expression' but found ',' on line 1 character 12 or The expression is not valid	`$map("sum([${MyListProperty}])", ",")`	When list-valued properties are used on an axis you must map the list-valued properties to a single string. The expression must contain a template to use for each value in the list (for example, an aggregation measure) and also a specification of how the list values should be connected in the resulting expression. The default expression obtained when first inserting the property is suitable for continuous axes where a simple listing of the columns included in the list-valued property is desired. In all other cases it must be manually modified.	Depending on what you want to display you must modify the default expression somewhat differently. If a simple listing of column names is desired (for example, if you want to show multiple columns on a bar chart value axis), then the expression in the example works fine. If you want to use an aggregation measure other than "sum" simply replace "sum" in the expression. If the list of columns is to be shown on a categorical axis, you must modify the default expression to something like this: `<$map("[${MyListProperty}]", "NEST")>` Categorical expressions must be surrounded by angle brackets, "<>", and you must also specify how different combinations of categories should be handled. Also, the current selection of columns in the list-valued property might be a mixture of continuous and categorical columns. Make sure that only columns of the same type are included in the property list. There are also more cases where the expression must be modified. See the section List-valued properties (multiple select) under Properties in expressions for more information.
Expected ':' but found '3' on line 1 character 5	`${TimeSpanProperty}`	If you try to use a TimeSpan, Date, Time or DateTime property in an expression, the expression language will not be able to interpret it correctly without some manual editing. First, it needs quotation marks around the property name. This will interpret the property as a string and you will be able to get rid of the error. Second, you must use one of the conversion functions to actually interpret the value as a TimeSpan, Date, Time or DateTime.	Put quotation marks around the property name and use the corresponding conversion function: `TimeSpan("${TimeSpanProperty}")` You can also right-click on the property in the Available properties for column list (in the installed client) and select Insert as Value from the pop-up menu instead: `TimeSpan(DocumentProperty("TimeSpanProperty"))`
{Table is undefined in ${{Table}	`${{Table}}.{MyProperty}`	If a name contains right curly bracket (}) it must be escaped by a backslash character (\).	To access the data table property MyProperty in a data table that is named "{Table}" you must write: `${{Table\}}.{MyProperty}`

More about $esc and $csearch

$esc(expression)

The $esc() function is used to escape "]" which normally denotes the end of a column from column names and adds "[" and "]" to the column name. The brackets are required for column names containing space characters to be interpreted as columns. $esc() can be used together with the $csearch() function. The argument is an expression which could be a property value or a property function that starts with a dollar sign ($).

For example, let the data table "A Data Table" have three columns called "Column 1","Column 2[example]", and "Sales". The expression $esc($csearch([A Data Table], "Col*")) returns a list with two elements-the strings "[Column 1]" and "[Column 2[example]]]".

$csearch([Data Table], "search string")

The $csearch() function is used to produce a "filtered" list of column names. It allows you to select a number of columns from a data table using a limiting search expression. This function is likely to be used together with the $map() function. The first argument is a data table and the second argument is a string that contains the search expression to be used to determine what column names should be returned. The function returns a list of (unescaped) column names contained in the data table that fulfills the search expression.

For example, let the data table "A Data Table" have three columns called "Column 1", "Column 2", and "Sales". The expression $csearch([A Data Table], "Col*") returns a list with two elements-the strings "Column 1" and "Column 2". If the property MyTable contains the string [A Data Table] and the property MyA contains the string "Col*" then $csearch(${MyTable}, "${MyA}") will return the same result.

$csearch together with $map() and $esc() can produce column lists or calculations based on columns from list-valued properties. For example, $map("sum($esc($csearch(${MyTable}, "*")))", "+") is expanded to sum([Column 1])+sum([Column 2])+sum([Sales]) because the search expression * will return all columns in the table. The $esc() function is necessary if you want the strings in the list to be interpreted as columns and the column names contain space characters.

Note: $csearch() is primarily intended to be used in visualization axis expressions or included in data function argument expressions. While $csearch() is looking at all columns in a data table, including any calculated columns, it is less suitable for use in calculated columns. If used in a calculated column, cyclic dependencies might occur.

Data table or column names in preprocessor expressions

Because the preprocessor only does textual replacement without any context or knowledge of what is a column name and what is a number, there is no way to track when a column name has changed. This means that preprocessor expressions must be manually updated if such names change.

If possible for your purpose, use non-preprocessor syntax instead of preprocessor syntax. For example, you can use Integer(DocumentProperty("name")) instead of ${name} to convert the value to an integer, if the property is not already an integer.
If that is impossible, perhaps you can use an intermediate column that does not get renamed? For example, assume you have an original column called A. You can then add a calculated column, B, with the expression [A]. Then you can mix B with a preprocessor syntax to calculate C. When replacing data, and A is renamed to A2, the B column will update correctly (because it does not have any preprocessor syntax in it), and C will recalculate correctly because it only depends on B, which was not renamed.