Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
tracycrown
Creator III
Creator III

Change Date format

Dear Sir/Madam

Kindly advise how to convert report date YYYYMMDD to following formats in scrip :

1. MMM-YY

2. YYYY

3. MM

4. DD

Thank you

Tracy

20 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

Ah, that's a common weirdness in QlikView. And actually it isn't weird at all. All fields in QlikView are of type dual, meaning that they have both a numerical value and a string representation (or format). The MonthYear values in your data model are in reality dates with a day component. The first one is Oct 12, 2013, the second one is Oct 18, 2013. The string formatting that you use in your load script doesn't change the numerical value, but will assign a MMM-YY format string to that field.

The field values though remain different, while their representation looks the same. All objects in QlikView will list/order dimensions first on their numerical value.

Simple to solve: change the load script for MonthYear into something like:

LOAD Date,

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

:

and gone is your issue.

Best,

Peter