Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Why dates are sorted like this...?

Hi all,

I don't find reason why my dates are not being sorted correctly. I generate the "Fecha" field (which is date in spanish) using the file name and dates functions like this:

date(date#(mid(filename(),5,8),'yyyymmdd'),'mm/dd/yy') AS Fecha

The filename is structured like this: DPF_20160229.xlsx

If you noticed, 01/31/16 comes before 02/29/16, and so on...

I read the most common issue happens when the date is not correctly formated, however I think I'm doing it correctly.

Any help would be appreciated. Thanks a lot.

Untitled.png

1 Solution

Accepted Solutions
Ralf-Narfeldt
Employee
Employee

The format strings are capitalization sensitive, so you need to use Caps, like:

date(date#(mid(filename(),5,8),'YYYYMMDD'),'MM/DD/YY') AS Fecha


m = minute

M = month


http://help.qlik.com/en-US/sense/2.2/Subsystems/Hub/Content/Introduction/conventions-number-time-for...


View solution in original post

3 Replies
tresesco
MVP
MVP

Could you share a sample that demonstrates the issue?

Ralf-Narfeldt
Employee
Employee

The format strings are capitalization sensitive, so you need to use Caps, like:

date(date#(mid(filename(),5,8),'YYYYMMDD'),'MM/DD/YY') AS Fecha


m = minute

M = month


http://help.qlik.com/en-US/sense/2.2/Subsystems/Hub/Content/Introduction/conventions-number-time-for...


Not applicable
Author

Thanks Ralf... That was it. I changed from what I posted to this...

date(date#(mid(filename(),5,8),'YYYYMMDD'),'MM/DD/YY') AS Fecha

... as you said and it fixed the issue.