Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I've seen Canonical Date Henric Cronström and Linking to two or more dates from Rob Wunderlich. But I still confused.
This is the script for Canonical calendar:
DateLink:
LOAD
Case_ID, // Fact Key
[Receipt Date] as Date, // Fact Date
'Receipt' as DateType // Fact Type
RESIDENT CASE_TEMP; // from Case_Temp
LOAD
Case_ID,
[Aware Date] as Date,
'Aware' as DateType
RESIDENT CASE_REPORTS;
// Call Calendar Generator for field "Date"
CALL CalendarFromField('Date', 'CommonCalendar', '');
CALL CalendarFromField('Receipt Date', 'ICSRCalendar', 'ICSR '); // Create Calendar for OrderDate
CALL CalendarFromField('Regulatory Report Aware Date', 'ComplianceCalendar', 'Compliance '); // Create Calendar for ShipDate
This is the autogenerate date range script for Master calendar:
TempCalendar:
LOAD
Date($(vStartDate) + RecNo()) as TempDate autogenerate $(vEndDate) - $(vStartDate);
MasterCalendar:
Load
TempDate as Date
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
I also set up Variable Event Triggers and Field Event Triggers for Master Calendar.
What confused me are:
1. I am not sure if the set up is correct or am I missing anything? I guess I am not totally clear about the logic that how this works.
2. How can I use it for list box "Year, Quater, Month", Start date and End date Slider/Calendar Objects, as well as other chart or table objects? How should I set up these different date / date range objects based on my Master/Canonical calendar?
3. Shall I use Set Analysis: Sum( {$<DateType={'XXX'}>} Case_Count ) with all the objects listed above?
4. I am also using alternate state/assigned state to objects in different tabs. Do I still need to assign states to objects if I have Master/Canonical calendar? How can I achieve this goal?
Could anyone share with me any thoughts that help me think it though clearly?
Thanks a lot!
Becky
Thanks Rob. I finally figured it out yesterday!
One more question, how can I write up the code in script for "Quarter" when generating Final Calendar?
// Generate Final Calendar
LOAD
[$(_field)]
,year([$(_field)]) as [$(_prefix)Year]
,month([$(_field)]) as [$(_prefix)Month]
,day([$(_field)]) as [$(_prefix)Day]
,weekday([$(_field)]) as [$(_prefix)Weekday]
;
Thanks,
Becky
Hi Becky,
It would be something like:
,'Q' & ceil([$(_field)]/3) as [$(_prefix)Quarter]
The routine provided in the tutorial was just a sample. If you want to generate Calendars using a subroutine, you may want to consider using QlikView Components http://qlikviewcomponents.org
-Rob
Thanks Rob. I got the Quarter working.
Cannot open the link you shared...
Sorry. I've corrected the link.
-Rob
Thanks Rob. I will check it out:)
Have a great weekend.
Becky
Sub Calendar (_DateField,_CalendarName,_CalendarPrefix,_CalendarSuffix,_FullCalendar)
Let _StartTime = Now();
Let _CalendarName = If(Len('$(_CalendarName)')=0,'Calendar','$(_CalendarName)');
Let _CalendarPrefix = If(Len('$(_CalendarPrefix)')=0,'','$(_CalendarPrefix)');
Let _CalendarSuffix = If(Len('$(_CalendarSuffix)')=0,'','$(_CalendarSuffix)');
Let _FullCalendar = If(Len('$(_FullCalendar)')=0,1,0);
Let _DateField = PurgeChar(_DateField,'"[]');
"$(_CalendarName)":
LOAD
Distinct [$(_DateField)] as [$(_DateField)],
Text(Date([$(_DateField)])) as [$(_CalendarPrefix)DateText$(_CalendarSuffix)],
Year([$(_DateField)]) as [$(_CalendarPrefix)Year$(_CalendarSuffix)],
// week([$(_DateField)])&'-'&Year([$(_DateField)]) as [$(_CalendarPrefix)WeekYear$(_CalendarSuffix)],
week([$(_DateField)])&' Week '& Year([$(_DateField)]) as [$(_CalendarPrefix)WeekYear$(_CalendarSuffix)],
// 'Week '& week([$(_DateField)])&'-'&Year([$(_DateField)]) as [$(_CalendarPrefix)Week$(_CalendarSuffix)],
week([$(_DateField)]) as [$(_CalendarPrefix)Week$(_CalendarSuffix)],
'Week '&week([$(_DateField)]) as [$(_CalendarPrefix)Week_4$(_CalendarSuffix)],
Month([$(_DateField)]) as [$(_CalendarPrefix)Month$(_CalendarSuffix)],
Year([$(_DateField)])&'-'&Text(Date([$(_DateField)],'MM')) as [$(_CalendarPrefix)YearMonth$(_CalendarSuffix)],
'Q'&Ceil(Month([$(_DateField)])/3) as [$(_CalendarPrefix)Quarter$(_CalendarSuffix)],
AutoNumber(MonthStart([$(_DateField)]),'_MonthSerial') as [$(_CalendarPrefix)MonthSerial$(_CalendarSuffix)],
AutoNumber(QuarterStart([$(_DateField)]),'_QuarterSerial') as [$(_CalendarPrefix)QuarterSerial$(_CalendarSuffix)],
YearToDate([$(_DateField)]) as [$(_CalendarPrefix)YTDFlag$(_CalendarSuffix)],
AutoNumber(weekyear([$(_DateField)]) &'|'&week([$(_DateField)]),'_WeekSerial')
as [$(_CalendarPrefix)WeekSerial$(_CalendarSuffix)] ;
If _FullCalendar=1 Then
Load Date(_DateStart+(Iterno()-1),'$(DateFormat)' ) as [$(_DateField)]
While (_DateStart+(Iterno()-1)<=_DateStop);
LOAD
Floor(Min(Fieldvalue('$(_DateField)',RecNo()))) as _DateStart,
Floor(Today()) as _DateStop
//Floor(Max(Fieldvalue('$(_DateField)',RecNo()))) as _DateStop
AUTOGENERATE FieldValueCount('$(_DateField)');
Else
LOAD Num(Fieldvalue('$(_DateField)',RecNo())) as [$(_DateField)]
AUTOGENERATE FieldValueCount('$(_DateField)');
End If
/* clean variables */
Let _TotalTime = Round((Now()-_StartTime)*60*60*24,0.00000000001);
Let _StartTime = NULL;
Let _vDateStart = NULL;
Let _vDateStop = NULL;
Trace $(_CalendarName) created in: $(_TotalTime) seconds;
Let _TotalTime = NULL;
ENDSUB
Cal_Bridge:
LOAD
[incident id],
opendate as Date,
'Op' as Dateflag
Resident Fact;
Concatenate(Cal_Bridge)
LOAD
[incident id],
resolvedate as Date,
'Rs' as Dateflag
Resident Fact;
Concatenate(Cal_Bridge)
LOAD
[incident id],
closedate as Date,
'Cs' as Dateflag
Resident Fact;
Temp:
LOAD
Min(Date) as Mindate,
Max(Date) as Maxdate
Resident Cal_Bridge;
LET vmindate= Peek('Mindate',0,'Temp');
LET vmaxdate= Peek('Maxdate',0,'Temp');
DROP Table Temp;
Calendar:
LOAD
($(vmindate) + IterNo() -1) as Date
AutoGenerate 1
While
($(vmindate) + IterNo() -1)<= $(vmaxdate);
Master_Cal:
LOAD
Floor(Date) as Date,
Year(Date) as Year,
Month(Date) as Month,
MonthName(Date) as MonthName,
Day(Date) as Day,
YearToDate(Date) as YTD,
Week(Date) as Week
Resident Calendar;
DROP Table Calendar;