I followed the videos and blogs on creating a Master calendar and managed to get multiple working, with both normal, fiscal and canonical.
However when I start building a sheet the data calculation sees to void out and I go from having count results of 5000+ to a count of 400, I'm not sure if this is the data set or the calendar but this only seems to occur once I start building extra tables beyond a test table.
The calender script I'm using is as below, I'm not sure if this is the most efficient script and this is causing the problem.
DateBridge: Load 'Completed Date' as Flag_Date ,"Ref No" ,"Completed Date" as Canonical_Date Resident Data;
Load 'Received Date' as Flag_Date ,"Ref No" ,"Received Date" as Canonical_Date Resident Data;
Master_calender: Load date(TempDate) as Canonical_Date ,year(TempDate) as Year ,Dual('Q' & Ceil(month(TempDate)/3), Ceil(month(TempDate)/3)) as Quarter ,Dual('Q' & Ceil(month(TempDate)/3), Ceil(month(TempDate)/3))&'-'&YearName(TempDate) as QuarterYear ,month(TempDate) as Month ,WeekEnd(TempDate) as WeekendDay ,week(TempDate) as Week ,day(TempDate) as Day ,WeekDay(TempDate) as WeekDay ,date(monthstart(TempDate),'MMM YYYY') as MonthYear
; load MinDate + IterNo() - 1 as TempDate // returns all dates while MinDate + IterNo() - 1 <= MaxDate ;
Load date(min(FieldValue('Canonical_Date',RecNo()))) as MinDate //find max and min value of dates in chosen column only not just table ,date(max(FieldValue('Canonical_Date',RecNo()))) as MaxDate AutoGenerate FieldValueCount('Canonical_Date');
TRACE; TRACE ##### LOADING GENERAL CALENDAR #####;
TempCalDate: NoConcatenate LOAD DISTINCT DATEFIELD AS CalDate RESIDENT TABLE WHERE DATEFIELD IS IN ;
TEMP: NoConcatenate LOAD MAX(CalDate) AS MaxDate Resident TempCalDate ;
LET vMaxDate = peek('MaxDate',0,'TEMP');
TRACE == Calendar==;
Calendar: LOAD Date(CalDate,'DD-MM-YYYY') AS [DATEFIELD], week(CalDate) AS Week, Year(CalDate) AS Year, 'Q' & Ceil(month(CalDate)/3) AS Quarter, date(monthstart(CalDate), 'MM-YYYY') AS MonthYear, AddYears(date(monthstart(CalDate), 'MM-YYYY'), -1) AS MonthYear_LAST, AddYears(Date(CalDate),-1) as DAY_LAST, Month(CalDate) AS Month, If(CalDate <= Today() And CalDate >= SetDateYear(Today(), Year(Today())-1), 1, 0) AS Last12Months RESIDENT TempCalDate Order By CalDate desc ;
