Master Calendar/Canonical Calendar

[#1]

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

[rwunderlich]

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

[#3]

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

[rwunderlich]

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

[#5]

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

[Anil_Babu_Samineni]

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

[#7]

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

[Anil_Babu_Samineni]

Might be Helpful

Selecting Arbitrary Date Ranges - YouTube

[#9]

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

[rwunderlich · Accepted solution]

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