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

[#1]

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

[sunny_talwar]

Try this

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

[#3]

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

[sunny_talwar]

In the script I guess

[martinpohl]

It should be used in the load editor

Regards

[dwforest]

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]

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.

[rwunderlich]

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]

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