topic Re: create date field from YearMonth field in QlikView

create date field from YearMonth field

Thu, 20 Mar 2014 07:26:22 GMT

hello.

is there anyway to create date field from YearMonth Field?

YearMonth

201301

Date

20130101

20130102

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

Best

Brad

Re: create date field from YearMonth field

PrashantSangle — Thu, 20 Mar 2014 07:39:55 GMT

Hi,

You can create date using

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

Regards,

Re: create date field from YearMonth field

er_mohit — Thu, 20 Mar 2014 07:41:26 GMT

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

Re: create date field from YearMonth field

Thu, 06 Nov 2014 19:28:06 GMT

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?

Re: create date field from YearMonth field

Thu, 06 Nov 2014 19:38:53 GMT

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

Re: create date field from YearMonth field

PrashantSangle — Fri, 07 Nov 2014 05:51:35 GMT

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