create a SheetLoop (dynamic loop) in Excel Template

SheetLoop or Dynamic Loop over a field

Sense Excel / AnalyticsGate offers a function called “Sheet Loop” for use with reporting content authored for the On-Demand and Distribution capabilities of AnalyticsGate Reporting Suite. When a Sheet Loop is applied, the AnalyticsGate Reporting Suite Engine will create a discreet Excel worksheet tab filtered for each member of the field/dimension specified. To apply a sheet loop, right mouse click on the worksheet you would like to include it within.  

Use the drop-down box to choose the Field/Dimension you would like the loop applied to.
Once the dimension/field is selected, a dialogue box will show the syntax created by the selection.

Export Root Node

You also need to specify whether or not you would like to export the root node and
if you would like a dynamic name applied to the worksheets other than the dimension values themselves.

Export Root Node:

If you were to specify a Sheet Loop for a field/dimension called “Year” which includes values “2017” “2018” and “2019”, your output workbook would have the below worksheets included:

With Export Root Node Checked/True:

Worksheet 1: 2017

Worksheet 2: 2018

Worksheet 3: 2019

With Export Root Node Unchecked/False:

Worksheet1: 2018

Worksheet2: 2019

The default value of Export Root Node is Checked/true.

Sheet Name:

The default setting is to name the individual worksheets with the values of the field/dimension specified in the Sheet Loop.

Having “Sheet Name” unchecked or the “Sheet Name” formula empty would name the worksheets “2017”, “2018” and “2019” respectively.

You can also use a Qlik Sense formula to dynamically name the worksheet by checking the “Sheet Name” box and if the formula output meets the following criteria:

1. The formula output values do not exceed 30 characters. This is an Excel limitation and will be truncate all characters above the max of 30.

2. The formula output values do not include any special characters not allowed in an Excel worksheet name such as ?, /, (, ), ] etc.

Example: The formula below;

=only([Fiscal Year])&’|’&’Report’

would create and name a worksheet for each possible value of [Fiscal Year] concatenated with “|Report” as the engine makes its dynamic selections for [Fiscal Year] like below:

2017|Report

2018|Report

2019|Report