Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Converting a number to MonthYear in MasterCalendar

I am new to Qlik, and previously, I found a post from March, 2014 where there was an example of a MasterCalendar that created a CalendarMonthAndYear field. When I use the CalendarMonthandYear field, it has a number in it like 41275, 41306, ... when the field is displayed.  How do I change the field so it is represented by a date like 201201, 201202, ...?

9 Replies
Not applicable
Author

Try =date(41275,'YYYYMM'). Hope it helps.

maxgro
MVP
MVP

=year(floor(41275.41306)) & num(month(floor(41275.41306)), '00')

Not applicable
Author

I believe I found the answer in Properties for that field, I went to the number tab, made the numeric field a Date, and then entered the format pattern as YYYYMM, and for now, it appears that it worked.

from your solutions above, where would I place the =date(41275,'YYYYMM'), and also there are many, so would I have to do this for each number?

Not applicable
Author

Replace 41275 with the column name. Ex: date(YEAR_DATE, 'YYYYMM'). You can add this as part of the load scrip or as calculated dimension.

MarcoWedel

Although changing the document standard format of this field in the num-tab is possible, I recommend to load this field using the already proposed

LOAD somefields,

           Date(fieldname, 'YYYYMM') as fieldname

FROM somewhere;

approach.

hope this helps

regards

Marco

Not applicable
Author

I tried adding this to my Load Statement:

LOAD

(ReportPeriod >= '201201') AS CalendarMonthAndYear,

Date(CalendarMonthAndYear,'YYYYMM') AS RptYearMonth,

But, I am getting an error indicating that the field CalendarMonthAndYear cannot be found.

Can you tell me what I am doing wrong in my Load Statement?

Not applicable
Author

I see that you already have a field ReportPeriod from the source in the 'YYYYMM' format. If ReportPeriod is not in 'YYYYMM' format, then you should use

date(ReportPeriod, 'YYYYMM')>= '201201' as RptYearMonth.

Please upload a sample file if you are not clear.

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try using below script

LOAD

*,

Date(Date#(CalendarMonthAndYear,'YYYYMM') AS RptYearMonth;

LOAD

*,

(ReportPeriod >= '201201') AS CalendarMonthAndYear

FROM DataSource;

OR

LOAD

*,

DAte(ReportPeriod, 'YYYYMM') AS CalendarMonthAndYear //If date is in 41745 format

FROM DataSource;

Hope this helps you.

Regards,

jAGAN.

Not applicable
Author

Hi,

Date(Fieldname,'YYYYMM) as New_date