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
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
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
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
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
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
Can i know, What do you want Achieve? So that, Might be get chance to help
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
Might be Helpful
Selecting Arbitrary Date Ranges - YouTube
Thanks Anil. I acutally created what I have based on this video.
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