create date field from YearMonth field

[#1]

hello.

is there anyway to create date field from YearMonth Field?

YearMonth

201301

Date

20130101

20130102

20130103....................................

Best

Brad

[PrashantSangle · Accepted solution]

Hi,

You can create date using

=Date(Date#(fieldname,'YYYYMM'))

Regards,

[er_mohit]

Create master calendar Link with Yearmonth field

// Calender

LET vDateMin = Num(MakeDate(2013,1,1));

LET vDateMax = Floor(monthEnd(Today()));

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Calendar:

LOAD Distinct

Date(TempDate,'YYYYMMDD' ) AS CalendarDate,

// Standard Date Objects

Day(TempDate) AS CalendarDayOfMonth,

Month(TempDate) AS CalendarMonthName,

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

Date(MonthName(TempDate),'YYYYMM') as CalendarMonthAndYear

RESIDENT TempCalendar

ORDER BY TempDate ASC

;

DROP TABLE TempCalendar;

link the CalendarMonthAndYear field with your table of month year field

[#4]

I am new to Qlik, and I was able to get the above Master Calendar to work, but when I use the CalendarMonthAndYear field, it has a number in it like 41275, 41306, ... How do I change the above so I get the representation of the date like 201201?

[#5]

year(date) & num(week(date),00) as YearWeek

[PrashantSangle]

Hi,

You are getting date field converted in num field.

So you can get it that field into list box->Property->Number->select Dateformat->give format which you want.

Or simply give date(dateFieldName,'YYYYMM')

Regards