The Pitfalls of NPrinting Filters – Part 1 (Dates and Duals)

Moving from NPrinting 16 to NPrinting 17 (and higher) brought to my attention one very interested aspect – Filters.

NPrinting 16 had quite well established and well-developed filters functionalities allowing users to filter dimensions values, apply bookmarks, apply filters based on value searches, create measure filters (like: Sales > 250), variable filters/variable inputs. All this in the world of NPrinting 17 is turned on its head, shaken and ultimately changed.

What are the changes:

Data Types – NPrinting 17 recognizes tags or datatypes of fields you are trying to filter. From my observations (which I have learned hard way) i came to following conclusions:

If field is loaded as dual, although it is shown usually as a text in UI the underlaying data is considered as numeric/integer and requires in NPrinting numeric filter. This is the most common mistake and often people get caught with dimensions like “Month” where they choose filter: “Value is: Jan and it does not work – as NPrinting is expecting them to choose “Numeric Value is: 1”

On screenshot below dual values on UI are shown as text by default, but you can change their UI format to numbers. This is clearly indicated when you preview field tags in data model.

QlikView screenshot showing how dual value can be shown as a number and text

Date filters – Here we have 3 traps:

First one is that date most likely is a dual. You probably see on UI dates formatted to you liking (MM-DD-YYYY or DD/MMM/YY etc…). This however does not matter if date is dual as all what you need is numeric value of your date. Dates can be stored as sequential serial numbers so that they can be used in calculations. December 31, 1899 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39447 days after December 31, 1899. So, to filter your dates which are dual you have to give in the filter numerical value. This is highly dependent upon how you model your data in Qlik application and there is not much you can do about it once you have it already in NPrinting. At this stage it is, what it is!

This is what comes up when you start typing in value (but do not get carried away – it might be wrong suggestion)

Properly applied filter in NPrinting for date stored as dual should look like this:

Dual in dates is even less forgiving as it might not show time portion of it. For example, you see in Qlik Sense/QlikView field with date= 01/01/2008 which is stored as a timestamp and actually can be 01/01/2008 12:00 PM. This means that your numerical value would be 39448.5, so as you can see there is decimal point in your numerical representation of timestamp and makes the value different that 39448 which would be just date. My usual advice is to apply Floor() function during Qlik script load of datamodel. This removes decimals and ensures that they are stored in field.

On the same note you might have another trap when working with “Evaluate value” in NPrinting filters. If you use functions like MonthEnd(), WeekEnd() they will return timestamp as well representing end of period so for example if today is 15/02/2019, MonthEnd(Today()) will return 28/02/2019 11:59:59 PM. Therefore again you have to wrap your formula in Floor() function to return only integer/day.

sample of formula returning date for last day of previous week

The above scenarios are covered briefly by help document on: https://help.qlik.com/en-US/nprinting/February2019/Content/NPrinting/ReportsDevelopment/Static-dynamic-filters.htm#anchor-2

I always suggest going there for any reference as different versions of NPrinting bring new functionalities and work differently, so to find an answer to a problem you are facing with your version use help as first source of truth.

I will soon cover more about filters in NPrinting, so stay tuned!

cheers

Lech

Leave a Reply

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

WordPress.com Logo

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

Google photo

You are commenting using your Google 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