Why dates are sorted like this...?

[#1]

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.

[tresesco]

Could you share a sample that demonstrates the issue?

[Ralf-Narfeldt · Accepted solution]

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...

[#4]

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.