NPrinting Filter

[oanalung]

Hi,

I want to generate an NPrinting report with data from QlikView that will only  show data from a table based on a date column - I want to show data for previous month.

Date Column has the following format - Date_column = 2022 01

I have tried with Filters in NPrinting(Create Filter -Fields -Date_column - Value is - Advanced Search/Value is -> Date_column= Date(AddMonths(Today(),-1,'YYYY MM') but it's not working. 

Thank you

[Lech_Miszkiewicz]

Hi @oanalung , 

Lets cover the basics:

• NPrinting version:
  • NPrinting version you are using (17.3 indicated by tag you have chosen when creating post) does not support Advanced search filters
  • NPrinting version you are using (17.3) - is no longer supported and I urge you to upgrade to the supported version
• Date_column
  
  • how this field gets created in QlikView? - it is fundamental to establish its datatype and values stored behind it - please provide:
    • the exact script loading it to QlikView (including script which creates filed values and formatting)
    • screenshot of QlikView table viewer so we can understand what type of  data gets created
    • below two links describe the importance of establishing how particular field is created and the actual datatype used - please study those conversations to get understanding:
      • https://community.qlik.com/t5/Qlik-NPrinting-Discussions/Evaluate-Value-filter-not-working/m-p/18683...
      • study this topic carefully as it goes into details of the same problem as yours: https://community.qlik.com/t5/Qlik-NPrinting-Discussions/Extract-the-data-for-Current-Month-and-Prev...

Now last thing to understand are the functions you are trying to use: Below functions creates (I broke it down for you, also note red bracket below which was missing in your syntax):

• Date(AddMonths(Today(),-1),'YYYY MM')
  
  • Todays date "Today()" = which is 44614 (that is the value of date for 22-02-2022)
  • Todays date for last month "AddMonths(Today(),-1)" = which is 44583 (that is the value of date for 22-01-2022)
  • Date MASK on Todays date for last month "Date(AddMonths(Today(),-1),'YYYY MM')" = which again  is 44583 (that is the value of date for 22-01-2022 even if it only shows year and month when used with date() function. The actual value behind it is a dual with numerical being 44583)

What I tried to show you is that use of (Date()) function does not change anything as effectively the value returned is still the same and it is actual date - all what it does is just apply certain format. More about date function on Qlik help side: https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/Scripting/Formatting... 

The very poor solution to your query would be probably syntax for Advanced Search like below - assuming that you upgrade your NPrinting version to supported version with Advanced Search:

• TEXT(Date(AddMonths(Today(),-1),'YYYY MM'))
  
  • That would take the MASK of 'YYYY MM' from the DATE() function and use that as a TEXT search effectively searching for text '2022 01'

At the end I provide you with the links to documentation about the filters you are trying to use and links to topics on my blog where I describe issues with filters:

• https://nprintingadventures.com/2019/02/15/the-pitfalls-of-nprinting-filters-part-1-dates-and-duals/
• https://nprintingadventures.com/2019/06/26/nprinting-filters-part-3-advanced-search/
• Study this to understand that choice of right option is also important: https://help.qlik.com/en-US/nprinting/May2021/Content/NPrinting/ReportsDevelopment/Static-dynamic-fi..., as we would not use "
  value is" option for duals and numerical values and we wouldn't use "numeric value is" for text values (even if they are made of numbers)

Summary:

knowing how your field is created, its data type/tag and its values we would likely be able to provide you with different solution than the poor one with text() function.

cheers

Lech