Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Date conversion

I have a date in a field. I need to convert it to Mon-yr format (Like Feb-16) I want to do it in such a way that it will recognize it as a date for sorting etc. (Meaning when I sort it, it will put Dec-15 before Jan -16 and then Feb-16 etc) How do I do it?

1 Solution

Accepted Solutions
MVP
MVP

Re: Date conversion

Not sure how your date field looks now, but try this:

Date(MonthStart(DateField), 'MMM-YY') as DateField

5 Replies
MVP
MVP

Re: Date conversion

There are two steps involved: Interpreting your field values correctly when loading in, then format to your requested date format:

Get the Dates Right

Why don’t my dates work?

On Format Codes for Numbers and Dates

MVP
MVP

Re: Date conversion

Not sure how your date field looks now, but try this:

Date(MonthStart(DateField), 'MMM-YY') as DateField

MVP
MVP

Re: Date conversion

If for some reason the date is not read as date, you can try like this:

Date(MonthStart(Date#(DateField, 'ExistingDateFormat')), 'MMM-YY') as DateField

There is also MonthName() function which provide the output in MMM-YYYY format.

Monthname(DateField) as MonthYear

Check help for all Date and Time related functions: Date and time functions ‒ QlikView

MVP
MVP

Re: Date conversion

I think MonthName() will return a format like 'MMM YYYY', without the dash, still a useful alternative.

Vish, just take care that your dates are in fact holding a numeric representation, then the sort order will be correct (chronologic order) by default when sorting numeric.

That's why you need to take care that your dates are 'recognized', interpreted correctly when loading in. Either by setting a correct default format code in the load script, using an interpretation function like Date#() or retrieving the field values from a source where Qlik can determine that a date is retrieved as a number.

Then you can use any formatting function, like Date() or a transformation function like Monthstart() or MonthName(), Day(), Year(), etc.

Not applicable

Re: Date conversion

Awesome! Worked like a charm!