Date Column showing Text Filters instead of Date Filters when Filtering in Excel

[Ameya09]

I have a excel report with Cell color formatting done at excel level,which is why keep source format is not enabled.

Even though the column has been converted to required Date format(MMM-YY) in Excel ,it keeps showing Text Filters as below when the report  is generated :

Requirement is to show the column with Date Filters as below : 

I tried creating the column using date function at backend in the app but still it shows up as text.

[Lech_Miszkiewicz]

Hi, From NPrinting point of view there is not much you can do. Data is transfered to template and it contains date format which we can see is interpreted by excel since you have filters by year appearing. I am assuming the issue may be in the fact that in your template before report is created the column contains <fieldtag> (which is effectively text) and on such field you are creating the filter on. The fact that it later gets changed to date may be to late for excel for excel to properly set it up which is actually Excel problem and not NPrinting.

1 - if on the produced report you remove the filter and set it up again - does it work as expected then?

2 - Are there any text objects in that column left? like "-" / empty cells?

cheers

Lech

[Ameya09]

Hi Lech,

1 - On setting it up again,the same thing happens and text filters are seen.

2 - Yes there are values with '-' present in the column but similiar date format column exists in the report with '-' values and it is showing correctly as date.The screenshot attached above is actually of that column.

[Lech_Miszkiewicz]

Hi @Ameya09 ,

I checked this on my end and this is purely excel thing (Not NPrinting). It works for me so my assumption is that your date data coming out from qlik is not really a date (number) type hence is not properly recognized by excel. It is hard to advice you anything else without knowing how you create your date field, whether it is a measure or dimension, what column settings you have used etc.

cheers

Lech

[Ameya09]

Hi @Lech_Miszkiewicz 

Thanks for the reply.

It seems the field is coming as text from the query itself as I tried creating the report just by loading the Snowflake query and it still showed as Text Filter.

Do you know any way to resolve this ?

Even tried Text to Columns in Excel but still didnt resolve the issue.

[Lech_Miszkiewicz]

Hi @Ameya09 

It comes down to understanding you datatypes in QlikSense/QlikView. When loading data to Qlik you want to make sure your date field is "understood by Qlik" as date. It will be then stored as a Dual value in a Qlik data model and will have text/date "mask" of the date with the numerical value behind it.

I am explaining whole concept here:

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

When loading data from Snowflake you can use functions like Date#() to transform text to actual date in your Qlik load script:

https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/Scripting/Interpr... 

Those are the fundamentals of Qlik so you should not have any issues with it. Once you have your date column loaded as DATE data type (not only looking like a date) then nprinting side will work with it too.

[Ameya09]

Hi @Lech_Miszkiewicz 

I checked the datatype in the data model viewer and it is as shown in screenshot.

One thing I have noticed is when I replace the null values with say a random date using below expression : 

if(isnull(fieldname),makedate(2000,1,1),date(fieldname))

The filters are shown as Date,so I am assuming it is the null values causing the issue.

Now I have tried NullAsValue, set NullInterpret for resolving this but still it shows as text.

Do you have any ideas for resolving this issue?

[Lech_Miszkiewicz]

HI @Ameya09.

I will say it again - this is not Qlik nor NPrinting behaviour. It is Excel behaviour so if values delivered to excel are not recognized as "Excel Date Filters" then maybe you need to experiment with it and replace null values which by default would result in export as a string with "-" character. You could try replace those with character (like whitespace or empty string '').

Then there are regional settings of your NPrinting and local excel which needs to be in line with the date formats. The fact that date looks like date does not mean your excel knows it is a date! Have you checked data type of the field in excel after export from Qlik Sense?

 After generating report you can just start removing nulls and see if maybe number of nulls in your data set plays role or maybe when you remove them manually from excel.

You saw it by yourself that it is purely data issue so I dont know what other sugegstion I can give you.

cheers