Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Since the resolved date is null, which year should it be associated with? Is this based on createdate? Maybe you need Canonical Date here
Since the resolved date is null, which year should it be associated with? Is this based on createdate? Maybe you need Canonical Date here
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;