Using variables and expressions in your Excel report template

Add variables and expressions to your report template as objects. You can use a variable or expression to dynamically store a value or calculation that can be centrally modified and re-used across a report.

Variables

You can insert variables from Qlik Sense into the template. Like other template objects you create, a variable is added as an object in the template configuration, and is accessible from the Qlik add-in window in Excel. Add an instance of the variable object as a variable tag.

You can add both scripted variables and variables defined in sheet view.

Adding a variable

Do the following:

1. Click the icon on the top toolbar of the add-in.

2. Click Add variable. A list of available variables appears.

   

3. Click the icon to the right of an existing variable to add it.

   

4. The variable tag is inserted in the currently highlighted cell. The settings page for the variable appears. Under Expression, you can view a Preview of the current variable value.

   It is recommended to provide a unique Name to the variable so you can identify it in the add-in if you later need to change its settings.

5. Adjust any other parameters as needed.

Revisit any variable you have added at any point for modifications to its configuration, to locate it in the workbook, or to add another instance of it to the workbook. Click the icon in the add-in to open the Variables and expressions landing page, and click to navigate to the cell where it is inserted. Adjust any settings as needed in the add-in.

For information on other important actions you need to complete with variables, see the following:

• Formatting a variable or expression

• Removing a variable or expression

Expressions

Add expressions as objects using Qlik Sense syntax and functions. Like other template objects you create, an expression is added as an object in the template configuration, and is accessible from the Qlik add-in window in Excel. Add an instance of the expression object as an expression tag.

Popular expressions you might want to add include:

• =Count(distinct <your dimension field>): returns the number of unique values for a dimension field you define in the expression (for example, Customer or Product).

• =today(): returns the current date.

• =year(now): returns the current year.

Adding an expression

1. Click the icon on the top toolbar of the add-in.

2. Click Add expression. A list of available expressions appears.

   

3. Click the icon to the right of an existing expression to add it. Follow the same steps for adding an existing variable: Adding a variable.

4. If you need to create a new expression instead, click Create expression.

5. Configure the new expression by adding a Name and Description (optional), and then define the Expression.

   

6. Check the Preview value under the Expression field to make sure you have configured the expression correctly, and click Create.

The expression object is created and will be accessible in the add-in window. The expression tag is also inserted in the currently selected cell.

Revisit any expression you have added at any point for modifications to its configuration, to locate it in the workbook, or to add another instance of it to the workbook. Click the icon in the add-in to open the Variables and expressions landing page, and click to navigate to the cell where it is inserted. Adjust any settings as needed in the add-in.

For information on other important actions you need to complete with variables, see the following:

• Formatting a variable or expression

• Removing a variable or expression

Formatting a variable or expression

Apply native Excel formatting to the cells containing your variable and expression tags. This can include font styling, number formatting, and other customizations. The formatting is applied to the report output.

Removing a variable or expression

You can remove a variable or expression from the report template, and from the list of that object type in the add-in pane.

Do the following:

1. Select the cell where the object is added.

2. Click Delete in the bottom of the add-in window.

This removes the tags and the object from the corresponding object list.

If you instead use Excel features to delete an object, it will not be inserted in the generated report. However, the object will still appear in the list of added objects. This means you can easily re-add the object later.