Filters

[rsmithy]

I load an excel file with a date column  (myDate)  with date values in format  '2/1/2023'.     I set the DateFormat variable as SET DateFormat='M/D/YYYY';       After the excel file is loaded and displayed in a Qlik Sense table, it displays the numeric value of the date: 44958     

I correct the date column to month(myDate) to give the viewer the expected date format (2/1/2023).   However when the user filters the table based on myDate the filter ribbon shows the table filtered on a function:  =date(myDate).    The user does not know what this means.   

How can I display the date in the default DateFormat  and filter it without showing the user a Qlick Sense function?    I tried loading the date as Date(myDate) but the result is the same.

[neerajthakur]

In Data Load Editor do this Date(myDate,'M/D/YYYY') as myDate or In front end Make a Master Measure of that Date with Date function and in Label write Field Name (myDate) and use this in your filter.

[rsmithy]

Unfortunately,  neither solution worked.     A master measure of Date(myDate,'M/D/YYYY') with label MyDate returns the number part of the date.    Same with using the date function in the Load Editor.

[neerajthakur]

Please share screenshot of what you are facing and expected results.

Try Num#

[BrunPierre]

Hi @rsmithy, as below.

Date(Num#(myDate),'M/D/YYYY')

[Vegar]

It sounds like you got the correct expression for converting you excel column into a date. The issue is the Nazeing of that new field is "=date(myDate)". Is this a correct  understanding of the situation?

The simple solution to this is to rename your expression as you load it in the script.

LOAD

*,

date(myDate) as [My Date]

FROM Excel file;

[neerajthakur]

@Vegar He already has myDate column and in Filter Pane he is using Date(myDate) to convert the date to his specified format, but upon selection he is getting function name with field name Date(myDate) instead of myDate Column name, I suggested to do the conversion in backend or create a master dimension.

I believe this is the problem he is facing:

Using Master Dimension:

[rsmithy]

You have described the issue correctly.       

in Load script:    

But the MNTH displays the date number in the  filter.   Then make Master dimension:

It still shows a date number. 

[rsmithy]

I see what the issue is.    In excel both columns are of type date

My excel data was actually as shown in MNTH2

Date(MNTH1, 'm/d/yyyy') displays the date text,   while date(MNTH2,'m/d/yyyy') in either the load script or master dimension displays the date number.    Only in the filter does  date(MNTH2,'m/d/yyyy') display the text version. But then the date function is displayed in the filter ribbon.    

I would have thought the date function would convert either field to the desired text format as both fields are recognized as date numbers in Qlik Sense

Loading the excel,  i can see that MNTH2 appears as a number, while MNTH1 appears as text

However if I load  this,   

then myDate presents a date text in the filter, while MNTH2 presents a date number, even though MNTH2 = 44958

[neerajthakur]

Use Date# instead of Date for MNTH2