Video 8 - Using the Calendar Generation sub-function

[Michael_Tarallo]

In this video I will focus on a sub-function included with QDF that will generate what is known as a Master Calendar. The Master Calendar is used to add time context to your data. When analyzing any data, time is an important factor. Users will always be interested in viewing the data by time periods as well as performing comparative analysis between time periods.

In a data warehouse, a time dimension table is normally present. It is joined to the fact table on some date key and allows for your metrics and dimensions to then have date and time context. In some situations, the time dimension is not present but a date field is present in the fact table. If this is the case - you will be missing key information and receive inaccurate results when analyzing data over time, especially if there isn’t any data for a specific time period.

In such scenarios, it is a best practice to create a Master calendar by using the date field in the fact table and joining it with a master date table that contains all of the combinations and formats of the time periods needed.

[Anonymous]

Is it possible to designate the start month of the year for a Fiscal Calendar?  I thought the "Months Left Fiscal Date Optional" was for that but it doesn't seem to be working as I intended as seen below.

[Michael_Tarallo]

Hi Michael - I think the positioning of the arguments may be incorrect.

I just checked the calendar example that is included with QDF and I see this in the script:

CALL CalendarGen('OrderDate','OrderDate','3', '11/7/1996','8/13/1997');

Let me know if that helps.

Please note that the calendar example - is located here: drive:<folder>\0.Administration\1.Application\0.Example\6.Calendar-Example

I will also let Magnus Berg aware of this.

Mike T

[Magnus_Berg]

Hi and thanks Michael, as Michael T typed the correct syntax should be CALL CalendarGen('OrderDate','OrderDate','3'). Sometime in the development cycle the syntax order changed (most used switch first) and this did not reflect in the manual.

QDF Development Guide is updated:
http://community.qlik.com/docs/DOC-5306

Cheers

[Anonymous]

Thanks for the Quick Response.   I was able to reflect '4/1/2008' as Fiscal Year '2009' and 'Q1'  using SUB "CALL CalendarGen('Date','Calendar','9') though it took a bit of trial and error as I'm having a hard time interpreting the "Months Left Fiscal Date Optional" explanation in the Development Guide.

Edit: I just wanted to add that I previously used qlikview-components - A script library for Qlikview development - Google Project Hosting for my Master Calendar SUB in case anyone was looking for an alternative.  Both are good.  Thanks again for this Framework!

[paputzback]

At about 1:30 you show the Table Viewer and there are three tables. Later on after you generate the Calendar you get two more tables, I expected to see the one MasterDate table, but I do not understand where the fifth Calendar table came from. Also, when you scrolled through the data in the preview there were some odd looking dates. Can you give more detail on why this happened?

[Michael_Tarallo]

Hi Phillip - I'll ask mbg - @Magnus - can you take a look?

Regards,

Mike

[Magnus_Berg]

Hi Phillip, the "extra" detached table is not from the CalendarGen function, it has probably been there all along. But in the earlier scenes the data model was zoomed in hiding this field. Regarding "odd looking dates" there are several dates and time fields looking at time from different angels, also the function creates a link field to the data model. In the Development Guide there are complete documentation regarding all the fields generated by CalendarGen. Hope that this helps.

Regards

Magnus