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())
  • 
• 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:

• https://community.qlik.com/t5/Qlik-NPrinting-Discussions/Only-produce-report-that-are-not-empty-when-using-cycle/m-p/1586203#M24318
• https://community.qlik.com/t5/Qlik-NPrinting-Discussions/Condition-combined-with-cyle-field/m-p/30385?advanced=false&collapse_discussion=true&filter=location&location=forum-board:qlik-nprinting-discussions&q=cycle%20empty&search_type=thread

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.

Cheers!

Lech