Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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, ...?
Try =date(41275,'YYYYMM'). Hope it helps.
=year(floor(41275.41306)) & num(month(floor(41275.41306)), '00')
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?
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.
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
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?
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.
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.
Hi,
Date(Fieldname,'YYYYMM) as New_date