13 Replies Latest reply: Jan 22, 2014 5:53 PM by Henric Cronström RSS

    Master Calendar Question

    David Young

      I was studied the Master Calendar function last night.  I completely understand the logic and what the script creates.  I created a new application with the following information only:

       

                  LET vDateMin = Num(MakeDate(2000,1,1));

                  LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

                  LET vDateToday = Num(Today());

                  TempCalendar:

                  LOAD

                   $(vDateMin) + RowNo() - 1 AS DateNumber,

                   Date($(vDateMin) + RowNo() - 1) AS TempDate

                  AUTOGENERATE 1

                  WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

       

                  MasterCalendar:

                  LOAD

                   TempDate AS CalendarDate,

                   Day(TempDate) AS CalendarDay,

                   WeekDay(TempDate) AS CalendarWeekDay,

                   Week(TempDate) AS CalendarWeek,

                   Month(TempDate) AS CalendarMonth,

                   Year(TempDate) AS CalendarYear,

                   'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

                   WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,

                   Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear

                  RESIDENT TempCalendar ORDER BY TempDate ASC;

                  DROP TABLE TempCalendar;

       

                  LET vDateMin = Num(MakeDate(2000,1,1));

                  LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

                  LET vDateToday = Num(Today());

       

      From there a created a list box for each of the date data.  Now what I need help on is a couple good business reasons why I would use the Master Calendar.  I know I can search and find examples but I want to see what the members have done.  Any ideas would be awesome.

        • Re: Master Calendar Question
          Peter Cammaert

          Master Calendars are mainly created and used for two good reasons (but those aren't necessarily business reasons, it's more to do with usability I think):

           

          • use a Master Calendar if you don't want timelines with holes in them. If you just use the order date field to base your timeline upon, dates on which no orders were placed won't be there... Master Calendar = continuous timeline
          • use a Master Calendar (or a Link Table with dates and additional dimension fields) if you want to connect multiple facts tables that have differing date granularity (for example Actuals = day or weekly, budget = monthly) and neither one of them may filter the available dates/periods.

           

          Of course, YMMV.

           

          Peter

          • Re: Master Calendar Question
            Henric Cronström

            As soon as you have a date in your data, it makes sense to load a Master Calendar. You don't want to clutter your fact table with a bunch of calendar fields like Week and Month.

             

            A comment on your script: You do not need a resident load. Instead you should use a preceding Load. It's both cleaner and faster. E.g.

             

            LOAD   Date AS CalendarDate,

                         Day(Date) AS CalendarDay,

                         WeekDay(Date) AS CalendarWeekDay,

                         * ;

            LOAD  Date($(vDateMin) + RecNo() - 1) AS Date

                        AUTOGENERATE $(vDateMax) - $(vDateMin) + 1;

             

             

            HIC

              • Re: Master Calendar Question
                David Young

                Thank you.  That makes sense. 

                • Re: Master Calendar Question
                  David Young

                  Henric,

                   

                  Thank you for your suggestion.  I am still learning about Master Calendars and so forth.  It sounds to me whenever you have a date field in a table it might be good to create a Master Calendar.  I am going to continue to experiment with them.  Let me ask you a question

                   

                  I have a table with CreateDate and ChangeDate loaded.  I need a two charts one to show how items were created based on the CreateDate field.  I need another Chart to show how many items changed based on the ChangeDate.  The charts will be independent of each other so what I want is two list boxes.  One with the Months(Jan, Fed, etc) and the other Years(2011,2012,etc).  Can you make it so if a person selects a Year the chart shows how many items were created and the other chart shows how many items were changed?  If some selects a Month they get the same type of results and finally if they select both they get the same type of results.  Does this make sense?  I am guessing one calendar but have to combine the two date fields together.

                   

                  David

                    • Re: Master Calendar Question
                      Henric Cronström

                      Yes you can. You need to create a common date to represent both dates. This you do in the script by the following two Load statements:

                       

                      GenericDate:

                      Load TransactionID, CreateDate as Date, 'Create' as DateType resident Facts;

                      Load TransactionID, ChangeDate as Date, 'Change' as DateType resident Facts;

                       

                      Then you connect your master calendar to this new date. You need to use Set Analysis in the charts. The reason is that a item will link to two dates - it will be counted twice unless you use Set Analysis. Hence:

                       

                      Sum({$<DateType={'Create'}>} Amount)

                      Count({$<DateType={'Create'}>} ItemID)

                       

                      HIC

                        • Re: Master Calendar Question
                          David Young

                          Hi HIC,

                           

                          Sorry to be asking so many questions.  I still have to create a Master Calendar along with the information you provided in the email above correct.  I don't know why I am so confused with the Master Calendar concept but for some reason I am.  I want to make sure I use the best code when building a Master Calendar and would love to have and example of the load statement.

                           

                          David

                      • Re: Master Calendar Question
                        David Young

                        Thank you Henric.  You have helped me out with questions I had on Master Calendar before.  I have it in different responses and want to put it all together.  I was wondering if you could help me out providing me an example that combines all the steps together when creating a Master Calendar.  The best way, variables, etc.  I am going to give you the scenario and if you can fill in the gaps that would be awesome.  I have included some comments about the field in bold but they are not in script.

                         

                        GOAL:  To have three charts.  One for each of the Date Fields from my Load Statement.  The charts would be how many new IP's were created, how many IP's had their first training session, and how many IP's did we have go live.  We might have 10 IP's created, 15 IP's had their first training session, and 4 IP's went live.  The 15 IP's is strictly looking at the "Initial_Training_Scheduled_For__c" so it is capturing the actual number of trained IP's for that month regardless of when those IP's we created.

                         

                        I hope this makes sense.

                         

                        Here is my Load Statement:

                         

                        Accounts:

                        LOAD Id as AccountId,

                             Name as OrganizationName,

                             Industry

                           FROM

                        Account.qvd

                        (qvd);

                         

                        ImplementationPlans:

                        LOAD Id as IPID,

                             CreatedDate,  //This is the date the IP was Created//

                             Initial_Training_Scheduled_For__c, //This is the date the IP was first Trained//

                             Go_Live_Date__c //This is the date the IP was Live//

                             FROM

                        Implementation_Plan.qvd

                        (qvd);

                         

                        MasterCalendarIPCreate:

                        LET vDateMin = Num(MakeDate(2012,1,1));

                                    LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

                                    LET vDateToday = Num(Today());

                                   

                                    TempCalendar:

                                    LOAD

                                     $(vDateMin) + RowNo() - 1 AS DateNumber,

                                     Date($(vDateMin) + RowNo() - 1) AS TempDate

                                    AUTOGENERATE 1

                                    WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

                         

                                    MasterCalendar:

                                    LOAD

                                    TempDate AS CreatedDate,

                                    Month(TempDate) AS CalendarMonth,

                                    Year(TempDate) AS CalendarYear,

                                    Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear

                                    RESIDENT TempCalendar ORDER BY TempDate ASC;

                         

                                    DROP TABLE TempCalendar;

                         

                        MasterCalendarIPCreate:

                        LET vDateMin = Num(MakeDate(2012,1,1));

                                    LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

                                    LET vDateToday = Num(Today());

                                   

                                    TempCalendar:

                                    LOAD

                                     $(vDateMin) + RowNo() - 1 AS DateNumber,

                                     Date($(vDateMin) + RowNo() - 1) AS TempDate

                                    AUTOGENERATE 1

                                    WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

                         

                                    MasterCalendar:

                                    LOAD

                                    TempDate AS Initial_Training_Scheduled_For__c,

                                    Month(TempDate) AS CalendarMonth,

                                    Year(TempDate) AS CalendarYear,

                                    Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear

                                    RESIDENT TempCalendar ORDER BY TempDate ASC;

                         

                                    DROP TABLE TempCalendar;

                         

                        MasterCalendarIPLive:

                        LET vDateMin = Num(MakeDate(2012,1,1));

                                    LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

                                    LET vDateToday = Num(Today());

                                   

                                    TempCalendar:

                                    LOAD

                                     $(vDateMin) + RowNo() - 1 AS DateNumber,

                                     Date($(vDateMin) + RowNo() - 1) AS TempDate

                                    AUTOGENERATE 1

                                    WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

                         

                                    MasterCalendar:

                                    LOAD

                                    TempDate AS Go_Live_Date__c,

                                    Month(TempDate) AS CalendarMonth,

                                    Year(TempDate) AS CalendarYear,

                                    Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear

                                    RESIDENT TempCalendar ORDER BY TempDate ASC;

                         

                                    DROP TABLE TempCalendar;

                         

                        I believe I have correct syntax for the above Load Statement.    I have included a copy of my Dashboard view to help you out. 

                         

                         

                         

                        1. I created a ListBox for Industry ( I used the Industry Field)

                        2. I created a ListBox for Years (I used the CalendarYear Field)

                        3. I created a ListBox for Months (I used the CalendarMonth Field)

                        4.  The charts are for the three date fields I want to return results on:

                                  a.  Total IP's Created

                                  b.  Total IP's First Training

                                  c.  Total IP's Live

                         

                        This is where I am still a little confused.  How do I show results based on The Year, The Month, or Both depending on what options are selected?  My guess is through the Dimension and the Expression on each of the charts but not sure what it would look like.  I have tried different ones and must be using the wrong fields or calculations.  Can you give me some insight?

                         

                        Thanks

                        David

                          • Re: Master Calendar Question
                            Henric Cronström

                            I think you make it too complicated. Your three calendars link to each other, and you probably don't want that.

                             

                            There are two different solutions to this problem:

                            1) Create three different Master Calendars. Each field must have a unique name, so no field is named as in the other calendars.

                            2) Create one common, generic date for all three dates.

                             

                            You can implement both solutions in the same app.

                             

                            ====== Solution 1: ======

                            Don't use temp tables. Use preceding load instead:

                             

                            GoLiveCalendar:

                            LOAD

                               TempDate AS Go_Live_Date__c,

                               Month(TempDate) AS Go_Live_Month,

                               Year(TempDate) AS Go_Live_Year,

                               Date(MonthStart(TempDate), 'MMM-YYYY') AS Go_Live_MonthAndYear

                               ;

                            LOAD Date($(vDateMin) + RowNo() - 1) AS TempDate

                               AUTOGENERATE 1 WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

                             

                            Repeat this code for "CreatedDate" and for "Initial_Training_Date".

                             

                            With solution 1 you can select a specific creation month and see when training and go live occurs.

                             

                            ====== Solution 2: ======

                            Each Impementation Plan ("IPID") must link to 3 different dates, all stored in the same "Date" field. Hence:

                             

                            IP_Bridge:

                            Load IPID, CreatedDate as Date, 'Created' as DateType Resident ImplementationPlans;

                            Load IPID, Initial_Training_Scheduled_For__c as Date, 'Training' as DateType Resident ImplementationPlans;

                            Load IPID, Go_Live_Date__c as Date, 'GoLive' as DateType Resident ImplementationPlans;GoLiveCalendar:

                             

                            GenericDate:

                            LOAD

                               TempDate AS Date,

                               Month(TempDate) AS Month,

                               Year(TempDate) AS Go_Year,

                               Date(MonthStart(TempDate), 'MMM-YYYY') AS MonthAndYear

                               ;

                            LOAD Date($(vDateMin) + RowNo() - 1) AS TempDate

                               AUTOGENERATE 1 WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

                             

                            With solution 2 you can use one single Date as X-axis in a chart, and use expressions like

                               Count({$<DateType={'Created'}>} distinct IPID)

                               Count({$<DateType={'GoLive'}>} distinct IPID)

                            to count number of IPs created and number of IPs that go live.

                             

                            HIC

                        • Re: Master Calendar Question

                          Like HIC says - Then you dont need to fill your Fact tables with Week,Month,Year and so on.

                           

                          But many BI consultants also create a Date Dimension in their date warehouse which includes,Year,Month,Quarter and so on.

                           

                          Normally I tend to use both, because I can use the MasterCalendar as a template across all document i might develop.

                          • Re: Master Calendar Question
                            Srikanth P

                            Lets say, we have the Fact table with 100K rows from Last one Year history.

                             

                            Generally we have only 365 Date fields in one year. If you don't have calender table, you run the Year , Month, Week and some flags (YTD) on 100K rows and it take longer reloads time.

                             

                            If you have the small table having 365 rows and created all the Year, Month, Week & other flags in this table, it take very less time to create the Date dimensions.

                              • Re: Master Calendar Question
                                David Young

                                Thank you.   So here is my problem:

                                 

                                I have two dates one in the AcctSetup table and One in the Bill Table:

                                1. FiscalYearBeginDate which could be 7/1/2012, 8/1/2012, 9/1/2012, etc. (AcctSetup Table)
                                2. BillStartDate which could be any date so as an example 2/1/2013 (Bill Table)

                                 

                                I load my AcctSetup Table:

                                AcctNum

                                FiscalYearBeginDate

                                ......


                                I load my Bill Table

                                AcctNum

                                BillStartDate

                                ....


                                What I need to know is the following:

                                If FiscalYearBeginDate = 7/1/2012 then BillDate 2/1/2013 is Month 7 of Fiscal Year 2012, 8/1/2012 is Month 6 of Fiscal Year 2012, 9/1/2012 is Month 5 of Fiscal Year 2012, etc.

                                 

                                So for each BillStartDate I need to know which month and fiscal year that date falls into.


                                The AcctSetup Table will look like:


                                AcctNum         FiscalYearBeginDate

                                123                    7/1/2012

                                456                    8/1/2012


                                The Bill Table will look like:


                                AcctNum           BillStartDate                             So my results need to be

                                123                    2/1/2013                                   Month = 7 and Fiscal year = 2012

                                456                    2/1/2013                                   Month = 6 and Fiscal year = 2012


                                Does this make sense?


                                David

                                  • Re: Master Calendar Question
                                    Richard Benoit

                                    You need to add the fiscal year, month and other date related fields to the  MaterCalender table.

                                    Your offset is your TempDate field.

                                     

                                    Example:

                                     

                                    //Fiscaldata

                                    year(AddMonths(TempDate,3)) AS Fis_Year,

                                    'Q' & ceil(month(AddMonths(TempDate,3))/3) AS Fis_Quarter,

                                    num(month(Addmonths(TempDate,3))) AS Fis_Period,