Important: This article can also be used to work with expressions in Vizlib Input Form.
TABLE OF CONTENTS
- Referencing Column/Field Values with Column() Syntax
- Best Practice - Referencing with a Column/Field Name
- Best Practice - Referencing with a Column/Field Number
- Expression Editor
- Use Variable in Calculation
- Calculated Column Syntax - VZB_SUM / VZB_MIN / VZB_MAX
Referencing Column/Field Values with Column() Syntax
A powerful feature is the ability to reference current values in the Vizlib Writeback Table and Vizlib Input Form. We leverage the Qlik function Column() because it's a valid Qlik expression syntax. Instead of evaluating the Column() function, we replace that with the current value of the column/field you are referencing and then evaluate the expression.
Figure 1: Calculated Column Result
Other valid Column() syntax with Column/Field name.
We do not recommend referencing values using the Column/Field number as
Use Variable in Calculation
When you're working with calculated columns in Vizlib Writeback Table (and calculated fields in Vizlib Input Form) and using a Calculation that is stored in a variable, you want the Qlik Engine to evaluate the variable before it's used in the calculation in the table. In the Type settings for Calculated Column / Calculated Field, you can now enable the Use Variable in Calculation checkbox (Figure 3). This works for all Evaluation Types, Simple and Qlik Expression.
Figure 3: Use Variable in Calculation
Use Variable in Calculation is checked the expression in Calculation will be evaluated (the variable will be expanded) before it's evaluated again with column/field values from the table/form.
Use Variable in Calculation is unchecked (the default setting) the Calculation property will be used without evaluating it before it's being used in the table/form.
If you select Use Variable in Calculation and the Calculation uses column syntax, you'll see a warning displayed (Figure 4). This is because when the Qlik Engine evaluates the Calculation before it's used in the table/form, it will evaluate as an error because Column syntax in the context of the property panel is not valid. Uncheck Use Variable in Calculation to solve the issue.
Figure 4: Calculation Warning
Calculated Column Syntax - VZB_SUM / VZB_MIN / VZB_MAX
You can now reference the SUM/MIN/MAX of a column in calculated columns for Vizlib Writeback Table by adding one of these three tokens before the column name in the expression editor.
- VZB_SUM references the total of all values in the column.
- VZB_MIN references the lowest value in the column.
- VZB_MAX references the highest value in the column.
Figure 5 shows an example of a SUM referenced on the Amount column.
Figure 5: Example Expression
Table 1 shows an example of a table displaying sales targets, where the column % of Target shows the target for each Sales Rep as a percentage of the Total shown in the Target column.
The expression used is shown here
Table 1: Sales Targets
|Sales Rep||Target||% of Target|