topic NPrinting Filter on Date that uses auto Calendar date fields in Qlik NPrinting

NPrinting Filter on Date that uses auto Calendar date fields

mjht — Wed, 17 Feb 2021 21:35:09 GMT

I am experimenting with advanced search filters. I want to filter the Beginners tutorial app to display the year 2013 and the months that begin with J (Jan, Jun, Jul). In the application there is one field Date to select for filtering the date - the date has autoCalendar fields - Date.autoCalendar.Date, Date.autoCalendar.Year, Date.autoCalendar.Month . How do I code a filter for the date parts? I was able to create a filter on the year 2013 by putting this expression in the Advanced Search value in nprinting >12/31/2012<1/1/2014. I am able to make selections in the Beginner's Tutorial app for the date parts but what appears in the selections are the date part field names and I tried using those in the nprinting filter but I can't get them to work.

Re: NPrinting Filter on Date that uses auto Calendar date fields

Lech_Miszkiewicz — Wed, 17 Feb 2021 23:09:28 GMT

First thing first...

Do you understand what those fields are, how they are created and what values they hold? Yes - VALUES!!!

Have a look at this part of autocalendar script :

[autoCalendar]: DECLARE FIELD DEFINITION Tagged ('$date') FIELDS Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'), Month($1) AS [Month] Tagged ('$month', '$cyclic'), Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'), Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'), Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'), Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'); DERIVE FIELDS FROM FIELDS [event_time] USING [autoCalendar] ;

You can notice that all values from there are actually derived from 1 date field, in my case it is [event_time] field. AutoCalendar fields are not present in data model like regural fields so you cannot create filter in NPrinting on for example YearMonth field.

Instead you need to create advanced search expressions which will filter event_dates to those values..

for example:

=count({<[event_time.autoCalendar.Month]={'Jan.','Jun.','Jul.'}>}event_time)>0

Be aware of datatypes you are trying to filter as well as syntax. More about that you can read here:

https://nprintingadventures.com/2019/06/26/nprinting-filters-part-3-advanced-search/

https://nprintingadventures.com/2019/02/15/the-pitfalls-of-nprinting-filters-part-1-dates-and-duals/

https://nprintingadventures.com

see below:

Re: NPrinting Filter on Date that uses auto Calendar date fields

mjht — Thu, 18 Feb 2021 16:11:26 GMT

Thank you for your reply - yes I do understand what the fields are and the values they hold. I was not clear on the syntax needed in the advanced filter to address them in nprinting. You have provided the syntax I was looking for and I will give it a try and report back.

Update --- worked perfectly! I was just missing the syntax needed. The expression in Advanced Search did it.

=count({<[Date.autoCalendar.Month]={'Jan','Jun','Jul'},[Date.autoCalendar.Year]={2013}>}Date) > 0

Thank you for the quick reply, Lech !

Re: NPrinting Filter on Date that uses auto Calendar date fields

Lech_Miszkiewicz — Thu, 18 Feb 2021 20:18:00 GMT

Awesome. Cheers!