topic Re: Format a date in MMMM YYYY format ? in QlikView

Format a date in MMMM YYYY format ?

fredericvillemi — Thu, 28 May 2015 08:56:34 GMT

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

Re: Format a date in MMMM YYYY format ?

Joaquin_Lazaro — Thu, 28 May 2015 09:04:25 GMT

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

Re: Format a date in MMMM YYYY format ?

simospa — Thu, 28 May 2015 09:06:44 GMT

Hi,

try

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

Let us know.

Re: Format a date in MMMM YYYY format ?

PrashantSangle — Thu, 28 May 2015 09:06:57 GMT

Hi,

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

Regards

Re: Format a date in MMMM YYYY format ?

fredericvillemi — Thu, 28 May 2015 09:07:12 GMT

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 ?

Re: Format a date in MMMM YYYY format ?

Joaquin_Lazaro — Thu, 28 May 2015 09:11:26 GMT

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

Re: Format a date in MMMM YYYY format ?

fredericvillemi — Thu, 28 May 2015 09:15:23 GMT

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

Re: Format a date in MMMM YYYY format ?

Joaquin_Lazaro — Thu, 28 May 2015 09:20:25 GMT

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

Re: Format a date in MMMM YYYY format ?

Joaquin_Lazaro — Thu, 28 May 2015 09:23:32 GMT

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 ?

Re: Format a date in MMMM YYYY format ?

rubenmarin — Thu, 28 May 2015 09:25:11 GMT

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

Re: Format a date in MMMM YYYY format ?

Joaquin_Lazaro — Thu, 28 May 2015 09:31:51 GMT

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 !!!!

Re: Format a date in MMMM YYYY format ?

fredericvillemi — Thu, 28 May 2015 09:34:26 GMT

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

Re: Format a date in MMMM YYYY format ?

Joaquin_Lazaro — Thu, 28 May 2015 09:41:33 GMT

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