Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

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

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

View solution in original post

5 Replies
swuehl
MVP
MVP

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

sunny_talwar

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

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

sunny_talwar

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

swuehl
MVP
MVP

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
Author

Awesome! Worked like a charm!