Master Calendar - date field not mapping to year as date is null

[leivers1]

I have a master calendar which is based off resolved date, see below.

Since the Resolution Date contains "NULL" Values, as not  everything has been "Resolved" this means anything with a null resolved date is not being mapped to a year. Is there a better way to create my calendar

Thanks

[Temp]:  
Load  
               min(resolvedate) as minDate,  
               max(resolveddate) as maxDate  
Resident [ISSUE];  
  
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));  
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));  
DROP Table Temp;

[TempCalendar]:  
LOAD  
               $(varMinDate) + Iterno()-1 As Num,  
               Date($(varMinDate) + IterNo() - 1) as TempDate  
               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);   
               
[MasterCalendar]:  
Load  
               TempDate AS resolveddate,  
               week(TempDate) As Week,  
               Year(TempDate) As Year,  
               Month(TempDate) As Month,  
               Day(TempDate) As Day,  
               YeartoDate(TempDate)*-1 as CurYTDFlag,  
               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,  
               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,  
               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,  
               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,  
               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,  
               WeekDay(TempDate) as WeekDay  
Resident TempCalendar
Order By TempDate ASC;  

Drop Table TempCalendar; 

[sunny_talwar · Accepted solution]

Since the resolved date is null, which year should it be associated with? Is this based on createdate? Maybe you need Canonical Date here

[leivers1]

Yes i think i would like it to be associated with resolved date if available and if null then associate the year with created date. Thanks i will look into that, hopefully solves it.

[sunny_talwar]

Then may be create a new field like this

If(Len(Trim(resolveddate)) > 0, resolveddate, createdate) as new_resolveddate

and then you calendar will be like this

[Temp]:  
Load  
               min(new_resolvedate) as minDate,  
               max(new_resolveddate) as maxDate  
Resident [ISSUE];  
  
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));  
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));  
DROP Table Temp;

[TempCalendar]:  
LOAD  
               $(varMinDate) + Iterno()-1 As Num,  
               Date($(varMinDate) + IterNo() - 1) as TempDate  
               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);   
               
[MasterCalendar]:  
Load  
               TempDate AS new_resolveddate,  
               week(TempDate) As Week,  
               Year(TempDate) As Year,  
               Month(TempDate) As Month,  
               Day(TempDate) As Day,  
               YeartoDate(TempDate)*-1 as CurYTDFlag,  
               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,  
               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,  
               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,  
               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,  
               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,  
               WeekDay(TempDate) as WeekDay  
Resident TempCalendar
Order By TempDate ASC;  

Drop Table TempCalendar;