NPrinting Filters – Part 3 (Advanced Search)

NPrinting June 2019 is out and with it the long-awaited advanced search filtering option. In my previous posts i wrote about different requirements which must be met to get NPrinting filters working properly. Community was buzzing mostly about being able to create wildcard filters with “wildcard match/like (*)” or “(>),(>=),(<=) ,(<)” approach. In other words, people wanted to be able to apply filters on data set which as a result could return multiple values.

Now when this feature is available how would you use it? The basics are covered in official documentation under “Creating a dynamic filter with advanced search” section and I will not cover it here. Instead I thought I would go through examples which are closer to business.

I – Case study principles

  • date field name – [Date]
  • dimension field – [Customer Name]
  • text field name – [Dimension 1]
  • measure field – [Sales Amount]

II – Dates

Advanced Search filter helps tremendously with this exercise. All sort of rolling dates like rolling 7,14,21,30 days, rolling months, last week, ytd etc. can now be easily handled in NPrinting filter. Although this feature was highly requested and is extremely convenient if you need to apply quickly such filter, I would still prefer to use flags built in Qlik script as I think they will work little bit faster especially on bigger data sets. Now let’s assume I want to filter:

  • Rolling last 7 days
    • Advanced search: =([Date]>Today()-7)*([Date]<=Today())
    • undefined
  • YTD
    • Advanced search: =([Date]>YearStart(Today()))*([Date]<=Today())
  • MTD
    • Advanced search: =([Date]>MonthStart(Today()))*([Date]<=Today())
  • Last 12 Months
    • Advanced search: =([Date]>AddYear(Today(),-1))*([Date]<=Today())

Obviously with this feature you have a lot of flexibility and can avoid creating flags. This is huge benefit to those NPrinting developers who have no direct impact on Qlik data model shape.

The other benefit of it is that dates and timestamps due to use of > < characters in advanced search are automatically recognised and don’t require conversion to numbers (which was very confusing and often created problem for users who did not study NPrinting manual closely).

III – Text

This is quite straight forward, but still useful. You can apply exact search (similar to regular NPrinting filter with “value is” attribute). You can also apply wildcard filters using *,? or ^ characters where each of them has a different function (details on how to use each of them are available on Qlik help page.

IV – Measures – “Enable Cycle” does not produce empty reports anymore!

This will be the long awaited functionality. It will allow to filter particular dimension based on true/false expression you put against dimension value. Let’s assume we have a master table with customers. That table contains all customers we have dealt with. In transaction table we only have few transactions as we just opened new show and we only want to report on those. Let’s assume we are reporting Sales by Customer Name. In such scenario to ensure that we are producing only reports for customers we had a sales with we can apply following filter:

  • Filter customers who have sales
    • Field: [Customer Name]
    • Advanced Search: =Sum([Sales Amount]) > 0

Obviously, we can deal with this in most scenarios by suppressing 0 values on charts or tables but in one scenario where we use “Enable Cycle” report function we couldn’t – until now!

Since you can create any expression in advanced search you can create a logic which for each dimension combination will check if your report can produce any values. Obviously problem can exist when you want to cycle using more than one field. In such scenario there is little bit more you have to do. You need to consider your data model and if possible create concatenated field (by concatenating dimensions you want to use in your cycle). then just apply advanced search filter against this field.

Advanced search is now a solution to cases like those reported on community topics:

Although it seems like just one small feature it is actually quite large step forward with NPrinting product improvement. I already see many different scenarios where this will become useful and will shorten designing and developing process.




  1. Thank you for the post, this will help us out tremendously. We import the users, filters, groups…with an excel spreadsheet. Can you tell me what the header should be for Advanced Search? I haven’t been able to find that one on any of their sites.
    Current Filter Headers: Name, Description, App, Enabled, Connection, Values, Numeric Values, Formulas


  2. Hi Lech,
    Thank you for this post. It is very helpful.

    Considering the last part: Advanced search on measures.

    I’m applying multiple cycles and want to use advanced search to filter reports only when variable (number of respondents) > value (10).

    You say that in the case of multiple cycles, you need to concatenate fields. Can you maybe explain more how this works? Maybe you have an article regardings this?

    Thanks in advance!

    Kind regards,



    1. Hi Daanp, that is quite straight forward.. Imagine you have Stores A and B and Products x,y,z… now each store sells all products so you get
      * Store A
      -product x = 10
      -product y = 5
      -product z = 2
      * Store B
      -product x = 7
      -product y = 9
      -product z = 1

      now if you cycle through stores and products and want to filter out only those with sales greater than (lets say) 5 then you actually need to apply filter on unique combination of the store and product, hence need for concatenation of those fields (store&’-‘&product) and that concatenation must be implementd in the load script as separate (new) field. Applying separate filters on stores and filters will not work in such case as you can clearly see from the example…

      If you wish to discuss this in details i suggest you open a discussion on qlik community in NPrinting section, provide a sample Qlik app and we can work of the sample there!



  3. I use the MTD filter in reports that I deliver daily and display data up to the previous day. The problem I have is on the first of the month. That day I want to pull the entire previous month, but as the filter is set as MTD, it obviously pulls no data. Is there a way to add a type of if/else statement so if it is the 1st of the month it pulls the previous month, else pulls MTD?


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s