Troubleshooting Property Expressions


Since properties can be inserted and interpreted in two different ways, there may be occasions where a seemingly correct expression does not work as expected. The following messages may be encountered when inserting properties in the Expression field of the Custom Expression dialog or the Insert Calculated Column dialog.

Some of the problems may 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 need to 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, 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 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 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.

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 need to map the list-valued properties to a single string. The expression must contain a template to use for each value in the list (e.g., 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 need to modify the default expression somewhat differently.

If a simple listing of column names is desired (e.g., 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 need to 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 may 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 needs to 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 need to use one of the conversion functions in order 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 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 needs to be escaped by a backslash character (\).

To access the data table property MyProperty in a data table that is named "{Table}" one 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]) since 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 may occur.

See also:

Properties in Expressions