Qlik Community

Qlik NPrinting Discussions

Discussion Board for collaboration on Qlik NPrinting.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Unable to apply "Year Month" (YYYY MMM) filter in Excel - QlikSense

I'm trying to filter a simple report displaying the same field on the following value using NPrinting June 2018:

Date("Invoice YM", 'YYYY MMM') AS "AsOfYearMonth"

I created the following "AsOfMonth - 2018 Jun." filter, this will be a dynamic filter in the future but just for testing i set the value to June 2018.

and added it to the report:

but unfortunately the report does not get filtered:

I have tried to apply the filter in the Designer -> Table properties but that didn't work either.

As well as changing the AsOfMonth - 2018 Jun. filter to Value is: 01/06/2018 with no luck.

Any help would be much appreciated.

Daniel

1 Solution

Accepted Solutions
Highlighted

Re: Unable to apply "Year Month" (YYYY MMM) filter in Excel - QlikSense

The other thing is to make sure your date is not a timestamp which would create decimal part of numeric representation


That is why i would also use Floor function before applying Date function... like

Date(floor(your-date,’you-format’))


This still will be numerical filter so in Nprinting you will have to use number like 43252 or any other number for respective date


In order to use date format in nprinting filter you would have to convert your date to text like 

Text(Date(floor(your-date,’you-format’))) and only then in nprinting you can use date format in filter


I think i covered all scenarios now


Cheers 

Lech

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

View solution in original post

9 Replies
Highlighted

Re: Unable to apply "Year Month" (YYYY MMM) filter in Excel - QlikSense

This was discussed many times on community - I have answered personally few times - use search (or google search) before creating duplicated topics:

Answers are in 4 below topics - it is all down to data type:

Nprinting Filters not working on Excel Documents

https://qlikcommunity.qliktech.com/thread/302084

https://qlikcommunity.qliktech.com/thread/295848

Dynamic Filter Nprinting 17

cheers

Lech

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Highlighted
Contributor II
Contributor II

Re: Unable to apply "Year Month" (YYYY MMM) filter in Excel - QlikSense

Hi Lech,

Appreciate the links and I checked them previously but I'm still not able to fix my issue.

Should I have just added to an old thread instead of creating a new one?

Considering my datatype is date I thought setting the value to "01/06/2018" or "=date(43252,'YYYY MMM')" might work but neither did.

Sorry but still confused

Highlighted

Re: Unable to apply "Year Month" (YYYY MMM) filter in Excel - QlikSense

Set it to

Numerical value =43252

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Highlighted

Re: Unable to apply "Year Month" (YYYY MMM) filter in Excel - QlikSense

By the way. Your data type is not date but it is likely integer nimeric with date formatting. Therefore filter must be numeric!


cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Highlighted

Re: Unable to apply "Year Month" (YYYY MMM) filter in Excel - QlikSense

The other thing is to make sure your date is not a timestamp which would create decimal part of numeric representation


That is why i would also use Floor function before applying Date function... like

Date(floor(your-date,’you-format’))


This still will be numerical filter so in Nprinting you will have to use number like 43252 or any other number for respective date


In order to use date format in nprinting filter you would have to convert your date to text like 

Text(Date(floor(your-date,’you-format’))) and only then in nprinting you can use date format in filter


I think i covered all scenarios now


Cheers 

Lech

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.

View solution in original post

Highlighted
Contributor II
Contributor II

Re: Unable to apply "Year Month" (YYYY MMM) filter in Excel - QlikSense

Thank you Lech,

Setting it to numerical did the trick.

Also the original is a datetime column but thankfully was already rounded down using floor() .

Daniel

Highlighted

Re: Unable to apply "Year Month" (YYYY MMM) filter in Excel - QlikSense

It gets very tricky. I personally think this should be reviewed by Qlik R&D as it makes work with NPrinintg difficult. Number of posts on community in regards to this topic is significant clearly indicating that you are not the only one having trouble with it.

Long term it would be great if NPrinting could follow both filtering (text and numerical) which in case of date fields becomes very important.

I learned my lesson when i used a lot of dual() functions when building one of my apps. Something like:

If(something = True,Dual('Yes',1), Dual('No',0) as field. Then when using this field you are facing 3 different scenarios:

  1. in set analysis you are looking at text representation
  2. in UI you could use both
  3. in NPrinitng you MUST use numerical representation.

It can be very confusing and difficult to use!

cheers

Lech

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Highlighted
Contributor II
Contributor II

Re: Unable to apply "Year Month" (YYYY MMM) filter in Excel - QlikSense

Hi Lech,

I actually thought you worked for Qlik, don't ask me why.

Couldn't agree more that they should bring in some standards to be used across the apps.

It feels as if NPrinting was developed as an add-on by a 3rd party and hence does not integrate well with Sense.

Let's see how they can keep improving the app.

Regards

Daniel

Highlighted

Re: Unable to apply "Year Month" (YYYY MMM) filter in Excel - QlikSense

Well - you are not far from truth - NPrinitng was a 3rd Party Application built by Vizubi team some time ago. It was a product which supported only QlikView back then. Few years ago Vizubi was acquired by Qlik and product got re-branded...

Product was re-built from scratch to support QlikSense as well.

cheers

Lech

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.