Video 8 - Using the Calendar Generation sub-function
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.
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.
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.
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.
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?
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.