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.
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:
The default value of Export Root Node is Checked/true.
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:
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.
The formula output values do not include any special characters not allowed in an Excel worksheet name such as ?, /, (, ), ] etc.
Example: The formula below;
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: