Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
This is a easy question:
This is an extract of my MasterCalendar:
MasterCalendar:
Load
TempDate As CalendarDate,
MonthEnd(TempDate) As ReportingDate,
week(TempDate) As CalendarWeek,
WeekDay(TempDate) as CalendarWeekDay,
weekstart(TempDate) As CalendarWeekStart,
Year(TempDate) As CalendarYear,
Month(TempDate) As CalendarMonth,
MonthName(TempDate) As CalendarMonthYear,
Day(TempDate) As CalendarDay,
'Q' & ceil(month(TempDate) / 3) As CalendarQuarter,
Thank you!
Sorry I've forgotten to post the question is : Why do I get the ReportingDate as a number and the Others as Dates?
Thank you!
and your question is????
Regards
date(TempDate) As CalendarDate,
Hi,
While creating qvd qlik engine convert value into numeber.
So if you want to ReportingDate in date format use date(ReportingDate) as ReportingDate
Regards
I didn't find ReportingDate field from your posting. Can you send full code
Or else
Date(Date#(ReportingDate,'YourDateFormatHere'),'YourRequiredFormatHere') as ReportingDate
OR
Date(Num(ReportingDate),'YourRequiredFormatHere') as ReportingDate
date(MonthEnd(TempDate)) As ReportingDate
Yes, but Why the other date fields were not converted?
Even I indicate ReportingDate as a date(ReportingDate) the engine insists in indicate it as a "Number" date format.
This is the hole code:
Load
TempDate As CalendarDate,
date(MonthEnd(TempDate)) As ReportingDate,
week(TempDate) As CalendarWeek,
WeekDay(TempDate) as CalendarWeekDay,
weekstart(TempDate) As CalendarWeekStart,
Year(TempDate) As CalendarYear,
Month(TempDate) As CalendarMonth,
MonthName(TempDate) As CalendarMonthYear,
Day(TempDate) As CalendarDay,
'Q' & ceil(month(TempDate) / 3) As CalendarQuarter,
Num(Month(TempDate)) as CalendarPeriod,
IF(TempDate>=addmonths(Date(Today()-Day(Today())+1),-12),1,0) as Rolling12MonthsFlag,
if(InYearToDate (TempDate, weekstart(today())-1, 0),1,0) as [Calendar YTD TY], // All Dates to Date this Year
if(InYearToDate (TempDate, weekstart(today())-1, -1),1,0) as [Calendar YTD PY], // All Dates to Date Last Year
if(InWeekToDate (TempDate, weekstart(today())-1, 0),1,0) as [Calendar WTD TY], // All Dates to Date this Week this Year
if(InWeekToDate (TempDate, weekstart(today()-7)-1, 0),1,0) as [Calendar Last WTD TY], // All Dates to Date Last Week this Year
if(InMonthToDate(TempDate, (today())-1, 0),1,0) as [Calendar MTD TY], // All Dates to Date this Year
if(InYear (TempDate, weekstart(today())-1, -1),1,0) as [Calendar FY PY], // All Dates Last Year
if(InYear (TempDate, weekstart(today())-1, 0),1,0) as [Calendar FY TY] // All Dates This Year
;
//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;
//=== Get min/max dates from Field ===/
LOAD
Num('$(vCalendar_Start_Date)') + IterNo() as mindate,
Num(MonthEnd(Today())) as maxdate
AutoGenerate 1
While IterNo() <= $(vCalendar_Number_Days_Between_Dates);
I have attached the snapshot of the table as is loaded (just the Reporting Date is in numbers)
Check this part, I've changed from num to simple date. Now you can check whether it willl or won't
//=== Get min/max dates from Field ===/
LOAD
Date('$(vCalendar_Start_Date)') + IterNo() as mindate,
Date(MonthEnd(Today())) as maxdate
AutoGenerate 1
While IterNo() <= $(vCalendar_Number_Days_Between_Dates);