Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi. I need help in this so much. This is the master calendar script. I got an error when I loaded the script. It says 'Field 'a' not found'.
Does anyone sees what's wrong with the script?
Thanks so much 
QuarterNAME:
LOAD * Inline [
Quarter , QuarterFullName
Q1 ,FIRST
Q2 ,SECOND
Q3 ,THIRD
Q4 ,FOURTH
];
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
MaxMinDates:
Load
Num(Min([Reporting Date])) as MinDate,
Num(Max([Reporting Date])) as MaxDate
Resident [Timesheet];
varMinDate = Peek('MinDate', -1, 'MaxMinDates');
varMaxDate = Peek('MaxDate', -1, 'MaxMinDates');
TempCalendar:
LOAD
$(varMinDate) + Iterno() - 1 as Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
TRACE VARMAXDATE: $(varMaxDate);
ManhourMasterCalendar:
LOAD
*;
Load
trim(date(TempDate,'DD/MM/YYYY')) as [Reporting Date],
day(TempDate) as [Reporting Day],
date(TempDate,'WWWW') as [Reporting Full Day Name],
year(TempDate) as [Reporting Year],
inyear(TempDate, date('$(varMaxDate)'),0) * -1 as [Reporting CY],
inyear(TempDate, date('$(varMaxDate)'),-1) * -1 as [Reporting First PY],
inyear(TempDate, date('$(varMaxDate)'),-2) * -1 as [Reporting Second PY],
inyeartodate(TempDate, date('$(varMaxDate)'),0) * -1 as [Reporting CYTD],
inyeartodate(TempDate, date('$(varMaxDate)'),-1) * -1 as [Reporting First PYTD],
inyeartodate(TempDate, date('$(varMaxDate)'),-2) * -1 as [Reporting Second PYTD],
InMonth(TempDate, date('$(varMaxDate)'),0) * -1 as [Reporting CMTD],
ApplyMap('QuartersMap', month(TempDate), Null()) as [Reporting Quarter],
quarterName(TempDate) as [Reporting Quarter Name],
yearname(TempDate) & 'Q' & Ceil(Month(TempDate)/3) as [Reporting Quarter Year],
inquarter(TempDate, date('$(varMaxDate)'),0) * -1 as [Reporting CQ],
inquarter(TempDate, date('$(varMaxDate)'),-4) * -1 as [Reporting First PQ],
inquarter(TempDate, date('$(varMaxDate)'),-8) * -1 as [Reporting Second PQ],
date(monthstart(TempDate),'MM') as [Reporting Month Number],
month(TempDate) as [Reporting Month],
date(monthstart(TempDate),'MMMM') as [Reporting Month Full Name],
monthstart(TempDate) as [Reporting Calendar Month Start Date],
monthend(TempDate) as [Reporting Calendar Month End Date],
date(monthstart(TempDate), 'MMM-YYYY') as [Reporting Month Year],
week(TempDate) as [Reporting Week],
week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [Reporting Week Year],
week(weekstart(TempDate)) & '-' & Month(TempDate) as [Reporting Week Month],
if(weekday(TempDate)<5, 'WEEKDAY', 'WEEKEND') as [Reporting Weekday or Weekend]
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
Drop Table QuarterNAME;
Drop Table MaxMinDates;
Accepted Solutions
