Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Not sure how your date field looks now, but try this:
Date(MonthStart(DateField), 'MMM-YY') as DateField
There are two steps involved: Interpreting your field values correctly when loading in, then format to your requested date format:
Not sure how your date field looks now, but try this:
Date(MonthStart(DateField), 'MMM-YY') as DateField
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
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.
Awesome! Worked like a charm!