Format a date in MMMM YYYY format ?

[fredericvillemi]

Hello,

Qlikview formatting of dates is probably the most complicated thing in the tool...

I have a generated calendar which creates fields like that (based on vMinDate and vMaxDate)

Load

  Date($(vMinDate) + IterNo() - 1) as DAY_CALENDAR,

  Year(Date($(vMinDate) + IterNo() - 1)) & '/' & Num(Month(Date($(vMinDate) + IterNo() - 1)),'00') as MONTH_CALENDAR

AutoGenerate 1

While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

I would like to add a formatted string field containing the MONTH_CALENDAR in the format MMMM YYYY (April 2015 for example)

How can i do to convert the Date($(vMinDate) + IterNo() - 1) into a formatted string ?

Thanks

[Joaquin_Lazaro]

That's good Ruben, but this method only shows the value of the system variables

SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';

SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

and LongMonthNames, LongDayNames

My suggestion is valid for a dual language environment !!!!

[fredericvillemi]

Ok, i understand we need mapping tables

that's nice

i hoped Qlikview provided a way to convert a date to string with format-code instead of specific functions and mappings

Thanks, I'll try that and tell you

[Joaquin_Lazaro]

You can use the DATE() function

DATE(Today(), 'DD/MMMM/YYYY') as DDMMMMYYYY

DATE(Today(), 'YYYY/MMMM/DD') as YYYYMMMMDD

DATE(Today(), 'DD/MM/YY') as DDMMYY

DATE(Today(), 'YY/MM/DD') as YYMMDD

DATE(Today(), 'DDDD/MMMM/YYYY') as DDDDMMMMYYYY

DATE(Today(), 'DDD/MMMM/YYYY') as DDDMMMMYYYY

or any format you need, but the text of the days and months will always depend on system variables (DayNames, LongDayNames ...)

Change the today() by your DATE field

Hope this helps too