Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

I need to make this date YYYY/MMM into DD/MM/YYYY

Hi Guys,

I need to make this date YYYY/MMM into DD/MM/YYYY, this is one hell of an excel spreadsheet, I don't know what it's doing to date, a right nightmare spreadsheet.

If date was YYYY/MMM you would assume its 2016/Jan, but it's doing 2016/001..... why do months need 3 digits?

Regards

1 Solution

Accepted Solutions
Highlighted

This one isn't tested, but this one is

Table:

LOAD Date(MakeDate(SubField([YEAR QTR], '/', 1), SubField([YEAR QTR], '/', 2) * 1), 'DD/MM/YYYY') as [YEAR QTR]

FROM

(ooxml, embedded labels, table is Sheet1);

View solution in original post

18 Replies
Highlighted

This isn't working?

Date(Date#(DateField, 'YYYY/MMM'), 'DD/MM/YYYY') as DateField

Make sure you environmental variables includes this:

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

Highlighted
Creator III
Creator III

Nope that didn't work.

Highlighted
Specialist II
Specialist II

Hi

Have a look, detailed explanation of date formats

QlikView Date fields

Hope it helps!!

Highlighted

Can you share few rows of data from your Excel file?

Highlighted
Creator III
Creator III

Hy there , go to object settings , and select number tab. There I select your date field and click Date , and in the right you can modify your date format.

Highlighted
Creator III
Creator III

Here's the field.

Thanks

Highlighted

May be try with this:

SET MonthNames='001;002;003;004;005;006;007;008;009;010;011;012';

Highlighted

Sample qvw attached

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='001;002;003;004;005;006;007;008;009;010;011;012';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Table:

LOAD Date(Date#([YEAR QTR], 'YYYY/MMM'), 'DD/MM/YYYY') as [YEAR QTR]

FROM

(ooxml, embedded labels, table is Sheet1);

Highlighted
Creator III
Creator III

That has worked, but i'll need to check if it doesn't mess up any of the other dates which I assume it will, is there any other way?