Canonical date filter not working

[JonathanKelly]

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');

[hansvillo]

Try this calender:

You can add your own "Weeldday etc" 

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
;

DROP TABLE TempCalDate;
DROP TABLE TEMP;