How to Create QlikView Bookmark Filters Using Dynamic Search Expressions

    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.

       
       
           
             

    Open Qlikview

                       
                Open-Qlikview.png                   
             

    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.

           
           
           
             

    Activate Multibox

                       
                Activate-Multibox.png                   
             

    Find or create a multibox or listbox containing the field(s) you wish to filter.

    1. Click on the caption of the box to activate it
           
           
           
             

    Begin

                       
                Begin-.png                   
             
    1. 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

                       
                Enter-Dynamic-Expression.png                   
             
    1. 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.
    2. Press Enter

    Some examples:

    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.

           
           
           
             

    Add Bookmark

                       
                Add-Bookmark.png                   
             

    Make any other desired selections, then save the Qlikview bookmark:

    1. Click on Bookmarks
    2. Select Add Bookmark
           
           
           
             

    Save Bookmark

                       
                Save-Bookmark.png                   
             
    1. Enter NPrinting Bookmarks as Bookmark Name
    2. You must flag Make this bookmark a document bookmark
    3. Make sure Include Selections in Bookmark is selected, so your dynamic search expression will be saved
    4. Select Make bookmark apply on top of current selection so that any other NPrinting filters won't be removed by the bookmark
    5. Click OK
           
           
           
             

    Create a New Filter Using a Bookmark

                       
                Create-a-New-Filter-Using-a-Bookmark.png                   
             

    Create an NPrinting filter using that bookmark, and apply to the NPrinting report.

    See "Creating NPrinting Filters Based on Existing QlikView Bookmarks"

           
           
           
             

    Result

                       
                Result.png                   
             

    After creating a new report with the CH268 as a table, see also "How to Create Excel Reports":

    1. Add the new filter to the report
    2. Click on Preview

    As you can see the excel report shows only the Countries with more than $100,000 in sales