Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Coverting Date from DD/MM/YYYY to MM/YYYY

Hello,

I have a table in which one of the fields is named date. When I uploaded this to Qlik the dates read:

42736

42767

42795

...

I'm unsure what format this is but I am able to convert this into DD/MM/YYYY using the expression Date(num#(Date,'#')).

This successfully changes the dates and they then read:

01/01/2017

01/02/2017

01/03/2017

But I want to have the dates read just 01/2017, 02/2017...

Is it possible to make the date read in that way? If so, please let me know.

Thanks in advance,

Alison

1 Solution

Accepted Solutions
sunny_talwar

So I am guessing you are doing something like this... are you?

Income:

CrossTable (MonthYear, Data)

LOAD Income,

     [42736],

     [42767],

     [42795]

FROM .....;

Now you need to do this

Income_Final:

LOAD Income,

     Date(MonthStart(Num#(MonthYear)), 'MM/YYYY') as MonthYear

     Data

Resident Income;

DROP Table Income;

View solution in original post

27 Replies
sunny_talwar

Try this

Date(MonthStart(DateField), 'MM/YYYY') as MonthYear

Not applicable
Author

Where should this be used, it the data load editor or as an master item expression?

sunny_talwar

In the script I guess

martinpohl
Partner - Master
Partner - Master

It should be used in the load editor

Regards

dwforest
Specialist II
Specialist II

Dates are stored as dual value, the number and a formatted display.

What you are seeing is the number.

in the top of the data load script, the default format is set:

SET DateFormat='DD/MM/YYYY';

change this to:

SET DateFormat='MM/DD/YYYY';

Then you can use:

Date("Date") As FormattedDate

dwforest
Specialist II
Specialist II

If the date value was a value other than the first of the month, this would force it to be the first of the month. Not sure that was the question, the sample data happened to match that pattern;

In this case, MonthStart would not be necessary to get the formatted output.

Matthew-Thornington

You can also do this from within the Data Manager when you edit a table.

date.png

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Sunny is correct that you should use the MonthStart() function to make the internal value of the dates to be the same. Otherwise you will get multiple values showing  "01/2017" in a listbox or chart dimension.

-Rob

dwforest
Specialist II
Specialist II

Yes, did not read correctly that the OP wanted MM/YYYY