Important: This article can also be used to work with expressions in Vizlib Input Form.
TABLE OF CONTENTS
- Expression Editor
- Column Syntax
- Column Order and Numbering
- Evaluation and Errors
- Resolving Syntax Errors
The syntax used for both methods replaces the value Column with the value held in the column for each row, so writing Column(6) in an expression represents the value held in column 6.
The example in this article will use the Qlik Expression method. We're looking to show a result for the expression
which should give the result shown in Figure 2, where Calculated Column displays the sum of the previous 2 columns.
Figure 2: Calculated Column Result
Column Order and Numbering
For expressions, Column parameters are numbered in their order of appearance in the Writeback Table or Input Form.
- Writeback Table - columns numbered left to right. Column(1) is the first field from the left.
- Input Form - fields numbered top to bottom. Column(1) is the first field from the top.
If you start to re-order columns, differences could develop between the columns in the table and the column list in the property panel. And if you're using column numbers in expressions, you may need to change the expression Column parameters to make sure your calculation works.
To make this easier to manage, you can now use the column name with Column.
- Instead of Column(1) you could use Column(Numeric).
- If the column name contains a space, you will need to use square brackets inside the Column brackets e.g. Column([Total Sales])
For an expression to successfully return a result, these steps need to be completed.
- The formula is analyzed via the extension, all Column values are replaced with values from the table. Column(9) and Column(10) in this example would be replaced by numbers held in column 9 and 10.
- The formula is sent to the engine for evaluation.
- The engine returns the result, which is displayed.
Small differences in syntax can make a real difference, and result in an error being returned. For example, when changes to an expression are saved by clicking Apply in the editor, an equal sign appears at the start of the expression.
This causes the formula to be sent to the engine without step 1 completing, so the engine tries to evaluate Column(9) and Column(10) and fails, as these are not numeric values. Figure 3 shows the results when the equal sign is included.
Figure 3: Incorrect Syntax
Similar errors can also be generated when other characters are used, such as
If you do return an error like these, the quickest way to resolve it is to manually change the expression without opening the editor. In Figure 4, removing the = sign before Sum in the Calculation text field resolves the error. The table is then saved and displayed in analysis mode.
Figure 4: Resolve Syntax Error