Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
leivers1
Contributor II
Contributor II

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

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 calendar04-01-2019 5-37-06 PM.png

Thanks

04-01-2019 5-47-36 PM.png

 

 

 

[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; 

 

 

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

3 Replies
sunny_talwar

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

View solution in original post

leivers1
Contributor II
Contributor II
Author

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;