Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
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

Tags (2)
1 Solution

Accepted Solutions
sunny_talwar
Not applicable

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

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;

27 Replies
sunny_talwar
Not applicable

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

Try this

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

Not applicable

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

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

sunny_talwar
Not applicable

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

In the script I guess

martinpohl
Not applicable

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

It should be used in the load editor

Regards

dwforest
Not applicable

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

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
Not applicable

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

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.

abj
Not applicable

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

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

date.png

rwunderlich
Not applicable

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

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
Not applicable

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

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