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]

HI  :

This is my calendar:

Calendario:

LOAD

  Num(Date(Fecha, 'YYYY/MM/DD')) as FechaOrd,

  Date(Fecha, 'YYYY/MM/DD') as Fecha,

  Date(Fecha, 'YYYY/MM/DD') as FechaCambio,

  year(Fecha) as Año,

  num(month(Fecha)) as Mes,

  day(Fecha) as Dia,

  weekyear(Fecha) + Week(Fecha)/100 as Semana,

  num(WeekDay(Fecha)) as DiaSemanaNum,

    Applymap('Dias',num(WeekDay(Fecha))) as NombreDia,

    Applymap('Days',num(WeekDay(Fecha))) as DayName,

    Applymap('DiasL',num(WeekDay(Fecha))) as NombreDiaL,

    Applymap('DaysL',num(WeekDay(Fecha))) as DayNameL,

    Applymap('Meses',num(Month(Fecha))) as NombreMes,

    Applymap('Months',num(Month(Fecha))) as MonthName,

    Applymap('MesesL',num(Month(Fecha))) as NombreMesL,

    Applymap('MonthsL',num(Month(Fecha))) as MonthNameL,

  'T' & ceil(month(Fecha)/3) as Trimestre,

  'Q' & ceil(month(Fecha)/3) as Quarter,

  year(Fecha) * 12 + num(Month(Fecha)) as AñoMesOrd,

  Applymap('Meses',num(Month(Fecha))) & chr(39) & (year(Fecha) - 2000)  as AñoMes,

  Applymap('Months',num(Month(Fecha))) & chr(39) & (year(Fecha) - 2000)  as YearMonth,

  InYearToDate(Fecha, today(), 0) * (-1) as %f_YTD,

  InYearToDate(Fecha, today(), -1) * (-1) as %f_LYTD,

  InMonthToDate(Fecha,today(),0) * (-1)  as %f_MTD,

  InMonthToDate(Fecha,today(),-12) * (-1)  as %f_LYMTD,

  InWeek(Fecha,today(),-1) * (-1)  as %f_PreviousWeek,

  InWeek(Fecha,today(),0) * (-1)  as %f_ThisWeek,

  IF (Date(Fecha, 'YYYY/MM/DD') = Date(Today(), 'YYYY/MM/DD'), 1, 0) as %f_Today

Hope this helps you

Joaquín

[simospa]

Hi,

try

Date(Date($(vMinDate) + IterNo() - 1),'MMMM YYYY')  as myMonth_Calendar

Let us know.

S.

[PrashantSangle]

Hi,

In script you have SET DateFormat change it to 'MMMM YYYY'

Regards

[fredericvillemi]

Thanks,

that's a good example

but is there a field where you have a text date in the format MMM or MMMM YYYY ?

it is not possible to take the format-code to convert from date to string ?

[Joaquin_Lazaro]

The Mapping Tables contain

Mon, Tue, Wed, Thu, Fri, Sat, Sun

Monday, Tuesday, Wednesday, Thurday, Friday, Saturday, Sunday

Translated into spanish as well; and more mapping tables for months.

The fields YearMonth and AñoMes content Jan'2015 or Ene'2015  ... and so on !!!

If you need some more, please ask me

Joaquín

[fredericvillemi]

No it doesn't work, it shows the same month for all months ... strange

[Joaquin_Lazaro]

fredericvillemin

You need mapping tables

Days;

LOAD Inline

Day, DayName

1, Mon

2. Tue

...

DaysL;

LOAD Inline

Day, DayName

1, Monday

2. Tuesday

...

The equivalent for months as well ... in my example are translated into spanish too

[Joaquin_Lazaro]

The field

Applymap('Months',num(Month(Fecha))) & chr(39) & (year(Fecha) - 2000)  as YearMonth,

has format Jan'15

If you modify the sentence to

Applymap('Months',num(Month(Fecha))) & ' ' & (year(Fecha))  as YearMonth

it will have format Jan 2015

Is this what you need ?

[rubenmarin]

Hi Frédéric, it should be:

Date($(vMinDate) + IterNo() - 1, 'MMMM YYYY')  as MonthYear

or if you want the month starting uppercase:

Capitalize(Date($(vMinDate) + IterNo() - 1, 'MMMM YYYY')) as MonthYear