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

[Anonymous]

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

[Lech_Miszkiewicz]

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

[Anonymous]

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

[Lech_Miszkiewicz]

Set it to

Numerical value =43252

[Lech_Miszkiewicz]

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

[Lech_Miszkiewicz · Accepted solution]

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

[Anonymous]

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

[Lech_Miszkiewicz]

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

[Anonymous]

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

[Lech_Miszkiewicz]

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