topic Re: Master Calendar/Canonical Calendar in QlikView

Master Calendar/Canonical Calendar

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Master Calendar/Canonical Calendar

rwunderlich — Thu, 28 Jul 2016 21:53:52 GMT

You have several concepts mixed here. The building of the DateLink table looks correct if you want to use a common calendar. After that, you are mixing the CalendarFromField sub (that I provided in my sample) with generation of a Temp Calendar table, which is a different approach. Then you ask about alternate states and the calendar, which may or may not be a relevant issue.

Ii suggest you break this down into smaller pieces, starting with a use case statement like "user should be able to select a single month and see count of cases opened and closed in that month". provide a small amount of sample data if you can. Even better if you can provide a sample QVW.

You may have several considerations here, depending on your final requirements. I suggest you tackle the requirements one at a time and learn as you go.

-Rob

Re: Master Calendar/Canonical Calendar

Fri, 29 Jul 2016 14:20:45 GMT

Hi Rob,

Thanks for taking your time and letting me know.

I was acutally built my metrics by tackle it step by step with folks help in Qlik Community.

What I have now is two different tabs - Case_Receive tab and Case_Submission tab. There are two different date for each tab. Receive_Date for Case_Receive tab, Submit_Date for Case_Submission tab. I may create the third tab or more with different date fields based on the future needs.

The steps helped me built this metrics are:

1. Enable user to select single Y, Q, M; (completed)

2. Enable user to select Start Date and End Date based on a master calendar; (completed but only apply for Case_Receive tab)

3. Assigned states to objects on Case_Receive tab and Case_Submission tab; (completed)

4. User could select Start Date and End Date (need to tackle this one, that's where I got confused about set different date fields for master calendar, commonCalenar etc.) Right now, the Start Date and End Date objects are not working on Case_Submission tab. I assumed the reason is related to master calendar.

Can you let me know how to tackle this issue?

Thanks a lot,

Becky

Re: Master Calendar/Canonical Calendar

rwunderlich — Sat, 30 Jul 2016 00:35:59 GMT

You are assigning StartDate and EndDate to variables, so they will have no connection to the calendars. You will need to either

1 ) Reference them in Set Analysis expressions (use a Straight Table, you can't code expressions in a Table Box).

2) Make selections using triggers.

I recommend you take neither of these options. Instead, eliminate the Start/End Date calendar objects. Instead let users select ranges from Year, Qtr and Month listboxes. If the user needs to select a range of actual dates, let then use a listbox for the Receive_Date. This will make your coding much easier.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Master Calendar/Canonical Calendar

Mon, 01 Aug 2016 15:46:32 GMT

Thanks Rob. Use a listbox is a simple solution. But I have to do the calendar objects.

How should I change for the StartDate and EndDate then they will conne t to the calendar? Which calendar you were mentioning here?

And do I need to create all these three tables: DateLink, CommonCalendar and Master Calendar?

If I need to create at least two of them, such as DateLink and Master Calendar, how can I link two of them together?

Best,

Becky

Re: Master Calendar/Canonical Calendar

Anil_Babu_Samineni — Mon, 01 Aug 2016 15:58:01 GMT

Can i know, What do you want Achieve? So that, Might be get chance to help

Re: Master Calendar/Canonical Calendar

Mon, 01 Aug 2016 16:01:56 GMT

Sure, thanks Anil.

I am trying to make the StartDate/EndDate objects work with two different date fields through a master calendar. You can find the sample QVW attachment from above post.

Please let me know any of your thoughts.

Thanks!

Becky

Re: Master Calendar/Canonical Calendar

Anil_Babu_Samineni — Mon, 01 Aug 2016 16:45:01 GMT

Might be Helpful

Selecting Arbitrary Date Ranges - YouTube

Re: Master Calendar/Canonical Calendar

Tue, 02 Aug 2016 19:31:03 GMT

Thanks Anil. I acutally created what I have based on this video.

Re: Master Calendar/Canonical Calendar

rwunderlich — Wed, 10 Aug 2016 01:28:59 GMT

Take a look at this sample to see if it helps you.

Qlikview Cookbook: Enter Date From To http://qlikviewcookbook.com/recipes/download-info/enter-date-from-to/

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Master Calendar/Canonical Calendar

Wed, 10 Aug 2016 13:29:02 GMT

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

Re: Master Calendar/Canonical Calendar

rwunderlich — Wed, 10 Aug 2016 18:30:01 GMT

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

Re: Master Calendar/Canonical Calendar

Wed, 10 Aug 2016 18:37:14 GMT

Thanks Rob. I got the Quarter working.

Cannot open the link you shared...

Re: Master Calendar/Canonical Calendar

rwunderlich — Wed, 10 Aug 2016 18:52:51 GMT

Sorry. I've corrected the link.

-Rob

Re: Master Calendar/Canonical Calendar

Fri, 12 Aug 2016 17:06:45 GMT

Thanks Rob. I will check it out:)

Have a great weekend.

Becky

Re: Master Calendar/Canonical Calendar

boraste-sagar — Sat, 29 Sep 2018 16:17:43 GMT

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

Re: Master Calendar/Canonical Calendar

boraste-sagar — Sat, 29 Sep 2018 16:27:53 GMT

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;