Filters in NPrinting – how hard can it be? (Part 3 – “Evaluate value”)

0
Written by Lech Miszkiewicz

Welcome to 2022 first NPrinting Adventures post. This one is one of the filter series. When creating a filter we first choose app, then connection, then field and at the end we are adding field values we want to filter. If the value needs to be calculated we can use (from dropdown) evaluate value option. Whatever we are evaluating expression formula or variable needs to return single value for the filter to work. This is not the only requirement though. Here is another important thing – data type. The returned value needs to be of the same data type as the field we are creating filter for. Let’s assume we have a “Month Year” field in QlikView or Qlik Sense data model and we want apply filter for last month. What users are seeing on the front-end is field with values like Jan-22, Feb-22, Mar-22 etc and here is important bit. For any filter in NPrinting you need to know what the actual values of the field are. You need to know how the field was created in Qlik load script. Fields like “Month Year” are usually created by script functions like this one: Date(Floor(MonthStart([SomeDate])),’MMM-YY’). By analysing this formula we see that effectively we created a date, first day of the particular month/year and we applied mask on in inf form of ‘MMM-YY’. From Nprinting filter perspective consequence is that NPrinting is awaiting numerical value in the filter so for Jan 22 the filter value should be 44562, for Feb 22 filter value should be 44593 for March 22 filter value should be 44621 and so on… as those are the numerical equivalent. With that in mind lets go through some examples

  • Current month filter: Evaluate value: = Floor(MonthStart(Today())) – (note there is no Date() function required as we are required to pass numerical value)
  • Last month filter: Evaluate value: = Floor(MonthStart(Today(),-1))

Let’s assume now that the field in your Qlik Sense is indeed a text field representing a string for month year like: ‘Jan-22’. For this to work in NPrinting you would need to use:

  • Current month filter: Evaluate value: = Text(Date(Floor(MonthStart(Today())),’MMM-YY’)) – (note that this time there is Date() function creating a dual format (text being MMM-YY and number whatever current date is) and Text() function forcing to pass only a text part of it.

Understanding duality of data in Qlik is important in this case and if you are inexperienced, you may not be aware of many traps this can lead to. I guess this is a main reason why we have posts on community like those mentioned below (please glance at them as there I go into detail to figure out why one of Qlik users filters didn’t work until we finally figured it:

cheers

Leave a comment