Expression language details

If you learn how the expression language works, the creation of valid expressions will be easier. This topic attempts to explain some basic functionality of the language.

If a column name contains characters other than letters, digits or _ (underscore), then they must be enclosed with "[" and "]" characters (square brackets). The square brackets are also required when the column name is the same as a function name, or when the column name begins with a digit. For example:

[Column 1]

[Sum]

If you apply <> around an expression, it is treated as categorical. For example:

<[Category column 1]>

Multiple columns are normally separated by a comma. For example:

[Column1],[Column2]

You can use the AS keyword to specify a new name for an expression, and hide the underlying expression. For example:

[Column1]/([Column1]+[Column2])AS Quota

Note that when the axis expression contains commas, you cannot rename the entire axis, but you can define one name for each part of the expression:

[Column1] AS [My first column], [Column2] AS [My second column]

Constants can always be used as an argument, even if the function description says that the argument has to be a column.

39+12*3

-(1-2)

See also Operators and Functions for complete descriptions of the available operators and functions.

Comments in expressions

If you create more complex expressions that need an explanation, it is possible to add comments to the expressions. Use '//' to specify that the text after the symbols is a comment.

For example:

Avg([Sales]) OVER Intersect([Cat], AllPrevious([Year]))
//Shows the average sales per category for all years, up until the current year.

[Sales]/1000 //The sales figure has been divided by a thousand to show values in kkr instead of SEK

Note that if you add comments to an expression on a visualization axis, it is expected that you continue using the custom expression dialog for all further editing. This is because the drag-and-drop operations affect and rearrange the expressions too much to be able to keep comments.

Escaping special characters

Because certain characters have a special meaning in the Spotfire expression language, you must perform some actions if you are going to use those characters for other purposes, such as including them in column names.

An escape character is a special character used to inform the expression language that the following character in a character sequence should be seen as a standard character rather than as an item performing its special purpose.

If a column name contains any square brackets then they must be escaped. Escaping of brackets is performed by adding extra brackets before and after the bracket: "[" and "]]". For example, if the column name is [Name], it would be written as [[Name]]] in an expression. When using the Insert Column button in the Add calculated column dialog (installed client only) to add columns, you will automatically get the correct escaping of the column name.

Case sensitivity

Variables, functions and keywords are not case sensitive: SUM(C1) = Sum(C1) = sum(C1)
Column name references are case sensitive.
Method call names are not case sensitive. All methods which are defined in the add-in framework can be used. See Operators and Functions for information about the different methods supported.

Expression results

An expression describes how a new column should be calculated. The newly created column will have the same number of rows as all the other columns in the data table. The default null handling behavior is that operations on null return null. This means that if a new column is calculated as [Column A]*2 and there are empty values on some rows in Column A, then the new column will have empty values on those rows as well.

Categorical expressions, NEST and CROSS

In custom expressions, categorical and hierarchical columns and expressions are written between angles, "<>". When more than one category is available within the expression, which combinations of categories to show must also be specified. This is done using the keywords NEST (which shows all actual combinations of values in the data) or CROSS (which will show all possible combinations of the values, including combinations that currently hold no data). All columns in the expression must be separated by "nest" or "cross" instead of a comma, and mixing the two combination options is not permitted.

<[Category column 1] nest [Category column 2]>

For example, if you have a data table containing some sales data for each month during two years, but the data for February is missing for 2020, the different options will give the following results:

Nest

Because there is no data available for February 2020, there will not be a bar (nor a placeholder for a bar) there. This visualization is set up using the Nest option on the category axis and it corresponds to the custom expression:

<[Year] NEST [Month]>

Cross

When the CROSS option is selected, all possible combinations of the categories can be displayed. This means that there will be a placeholder for the February column for 2020, even though there is no data available for February. The corresponding custom expression would be:

 <CROSS [Year] CROSS [Month]>

(The first CROSS is optional.)

Note: Even though the CROSS option is selected, you might not see the empty categories in the visualization depending on the axis mode. In the installed client, you can right-click on the axis and select Categories to view or change the settings. If Show filtered values is selected, then categories without any values will be hidden in the visualization. You must use Show filtered range or Show all values to see the empty category in this example.

Tip: In the installed client, the setting for NEST/CROSS, as well as the settings for the axis mode and how to evaluate the axis expression, can also be specified from the Visualization Properties > Category Axis > Advanced Settings dialog.

Validation of values

Empty values are generated whenever a column value from the data table is missing, when a calculation involves an invalid value, or by explicitly writing null in the expression. Results that are null, are displayed as (Empty) or simply left blank. Null values can be replaced using the SN() function.

When aggregating within a column, the invalid value will be ignored, whereas row-wise calculations between columns will result in invalid values each time one of the involved columns contains an invalid value.