Referencing Column/Field values in Expressions

Important: This article an also be used to work with expressions in Vizlib Input Form.

This topic contains the following sections:

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.

We've listed the cases where Column() syntax can be used in Vizlib Writeback Table or field values in Vizlib Input Form here:

  • The Calculation property for the Calculated Column data type.

  • Hide and Show conditions, namely

    • The Show field if property for Vizlib Input Form fields.

    • Form Groups, Captions, and Dividers in Vizlib Input Form and Vizlib Writeback Table Form View.

  • Numeric inputs e.g. Column("Slider") / 100

  • Validation Expression property - Validation using Qlik Expression.

  • Required if when evaluated on row level

If you would like to add the value of two columns together you can use a Calculation like the example shown here:

Column('Slider')+Column('Numeric')

The calculation should give the result shown in Figure 1, where the Calculated Column displays the sum of the Slider and Numeric columns.

Figure 1: Calculated Column Result

 

Best Practice - Referencing with a Column/Field Name

Single quotes, double quotes, square brackets, and no quotes can be used when referencing the column/field name in the Column() function. We recommend using single quotes because if you open the expression in the Qlik Expression Editor you will not get any syntax errors and it's not confused with Field referencing with double quotes.

Column('Slider')

Other valid Column() syntax with Column/Field name.

Column(Slider), Column([Slider]), Column("Slider")

 

Best Practice - Referencing with a Column/Field Number

We do not recommend referencing values using the Column/Field number as differences between the columns listed in the expression and the property panel can develop over time if any columns are re- ordered. If you're using column/field numbers in expressions, you may find you need to:

  • change the expression to make sure your calculation works.

  • use a reference with column/field name.

Column(1)

Columns/Fields are numbered in the order of their place in the property panel in the Writeback Table or Input Form starting from 1 for the top column/field.

  • Writeback Table - Columns

  • Input Form - Fields

 

Expression Editor

You can open the expression editor by clicking on thebutton where it's displayed in the property panel. The expression editor is useful for complicated expression and you need some help with the expression syntax. Figure 2 selects Calculated Column, scrolls to the Type section in the property panel, and opens the editor for the Calculation function.

Figure 2: Expression Editor

 

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 check box (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.

Note: The calculation will only work if live totals are enabled (see Appearance - Table Settings). If totals are disabled you'll return an error.

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

=Column('Target')/Column('VZB_SUM Target')

Sales Rep Target % of Target

A

50

22%

B

100

44%

C

75

33%

Total

225

100%

Table 1: Sales Targets