Formulas

You can create QlikView and Qlik Sense formulas and add them to reports in Qlik NPrinting. Formulas must follow QlikView or Qlik Sense formula rules and syntax.

Formulas are applied with and without numerical and time formatting. Qlik NPrinting sends the formula to QlikView or Qlik Sense for evaluation. Any numerical or time formatting must be applied by QlikView or Qlik Sense at the moment the formula is evaluated to its unique value. The value is exported from with whatever numerical or time formatting has been applied, and will not be altered by any attempt to apply similar formatting in the template.

Note that =getCurrentSelections() returns a unique string. It is composed by merging the selected values with a separator, but the function is allowed because this is a single string.

You cannot add an Aggr() formula, for example Aggr(Count(DISTINCT CustomerID), Salesman), because it returns multiple values. You can resolve this by concatenating the result of the Aggr(), for example concat(Aggr(Count(DISTINCT CustomerID), Salesman), ' - ').

Add formula

Do the following:

1. Select Reports in the Qlik NPrinting main menu, and then click Create report.
2. Enter a Title for the report.
3. Click Edit template to open the Template Editor.
4. Right-click the Formulas node, and then select Add formula.
5. Enter a name in the Add formula window that opens. For example, No. of Orders.

6. Enter your formula. For example, NumericCount (OrderID).

   The calculated result is visible in the bottom part of the Edit Formula window.

7. Click OK.

   You can add additional formulas if desired.

8. Drag the formula node tokens into the template and drop them onto the appropriate empty cells one at a time.

Format

Cells containing formulas or variables will appear in your report with formatting from the source until you apply formatting in Excel formats. They will then appear in your report with the formatting that you apply using Excel formatting tools.

Do the following:

1. Click the Toolbar icon to display the ribbon bar.
2. Apply appropriate formatting to the cell that contains the formula tag.
3. Click Preview to see the results of the changes you made.
4. Close Excel, and then if necessary adjust your formatting and preview again.

Add a level

You can insert formulas in levels. They will be evaluated for each level.

Do the following:

1. Right-click the Levels node, and then select Add levels.
2. Select the Connection to the QlikView document that contains the object you want. For example, select Sales Demo.
3. Select Year in the Select level window.
4. Click OK.
5. Drag the Year_Level node token onto a group of three consecutive empty cells.
6. Reposition <Year_Level> and </Year_Level> so that they are on rows above and below all formulas to be included in the level.

The rows containing the level tags will be eliminated during report generation. Leave room for the Year heading that we are going to include in the following procedure.

Add heading

Do the following:

1. Expand Year_Level.
2. Drag the Year node token into the template and drop it onto an empty cell within the Year_Level range.
3. Apply formatting from the toolbar.

Preview and save

Do the following:

1. Click Preview.
2. Check the report, close it, and then edit the template again if necessary.
3. Click Save and Close to save the template and close the Template Editor.