In NPrinting users can enter a QlikView expression that will return one value to select, see How to Create Static and Dynamic Filters Based on Fields. In order to filter a field in QlikView dynamically based off an expression that will return several items (such as '=sum(Sales) > 100000' to return all items with total sales over $100,000) the filter must be saved in a bookmark in QlikView, and then the bookmark added to the NPrinting report.
Multiple filters of this type can be saved under one QlikView bookmark, and added to an NPrinting report, they will be applied on top of each other using the same logic as filters applied by a QlikView user. These filters are evaluated every time NPrinting applies the bookmark filter, so the items selected by the filter can change each time the report is run.
First you need to create the bookmark in QlikView. Open the Qlikview document, and remove any other filters you do not want in your bookmark.
Find or create a multibox or listbox containing the field(s) you wish to filter.
- Click on the caption of the box to activate it
- First type '='. This tells QlikView that you wish to enter a dynamic search expression.
Note: as you type, the multibox will constantly filter for the items that will return 'true' to your expression. The expression '=' will not return 'true' for any item, thus the multibox will not display any items.
Enter Dynamic Expression
- Type in the dynamic search expression desired. These must be expressions in Qlikview syntax that will be evaluated as true or false for each item in the field. The items that return 'true' will be dynamically selected.
- Press Enter
In 'Country' type '=sum(Sales) > 100000' for the countries with more than $100,000 in sales.
In 'Salesperson' type '=rank(sum(Sales)) <= 10' for the top 10 selling Salespeople.
In 'Day' type '=sum(Invoice)<0' for the days when the total invoices were negative.
In 'Products' type '=count(distinct Orders) > 100' for the products which had more than 100 distinct orders.
Hit 'enter' to make the selection.
Note: you can apply several of these search expressions to the same field one at a time. The field will be filtered in the order in which you enter the selections.
Make any other desired selections, then save the Qlikview bookmark:
- Click on Bookmarks
- Select Add Bookmark
- Enter NPrinting Bookmarks as Bookmark Name
- You must flag Make this bookmark a document bookmark
- Make sure Include Selections in Bookmark is selected, so your dynamic search expression will be saved
- Select Make bookmark apply on top of current selection so that any other NPrinting filters won't be removed by the bookmark
- Click OK
Create a New Filter Using a Bookmark
Create an NPrinting filter using that bookmark, and apply to the NPrinting report.
After creating a new report with the CH268 as a table, see also "How to Create Excel Reports":
- Add the new filter to the report
- Click on Preview
As you can see the excel report shows only the Countries with more than $100,000 in sales