4 Replies Latest reply: Jul 9, 2014 3:13 AM by Harshal Patil RSS

    My Master Calendar Does Not Work

    BAIKANG CHEN

      Hi:

       

      I created a master calendar out of my fact table using the following script.

       

      MinMax:

      LOAD

        Min(FactDate) AS MinDate,

        Max(FactDate) AS MaxDate

      RESIDENT FactTable;


      Let vMinDate = Num(Peek('MinDate',0,'MinMax'));

      Let vMaxDate = Num(Peek('MaxDate',0,'MinMax'));

       

      Drop Table MinMax;

       

      TempCal:

      LOAD

        Date($(vMinDate) + RowNo() - 1) AS FactDate

      AutoGenerate

        $(vMaxDate) - $(vMinDate) +1;

       

      Calendar:

      LOAD

        FactDate,

        Date(FactDate) AS [Date],

        Year(FactDate) AS Year,

        Ceil(Month(FactDate)/6) & 'H' & Year(FactDate) AS [Half_Year],

        Ceil(Month(FactDate)/6) & 'H' AS [Half Year],

        'Q' & Ceil(Month(FactDate)/3) & '-' & Year(FactDate) AS [Quarter_Year],

        'Q' & Ceil(Month(FactDate)/3) AS Quarter,

        Month(FactDate) & '-' & Year(FactDate) AS [Month_Year],

        Month(FactDate) AS Month,

        Week(FactDate) & '-' & Year(FactDate) AS [Week_Year],

        Week(FactDate) AS Week,

        MonthStart(FactDate) AS [Month Start]

      RESIDENT TempCal;

       

      Drop Table TempCal;

       

      The script executed successfully.

       

      And I can see an association between my Fact table and the Calendar table in the table viewer.

       

      But when I go to the front and start using the the calendar as a dimension, it does not work.

       

      There is no data available when I make a selection in the calendar.

       

      I can only see values when the dimension is null.

      Capture.PNG.png

       

      Can anyone help me with this?

       

      I used the same method before and it worked.

       

      Thank you very much.

       

      PC

        • Re: My Master Calendar Does Not Work
          Jonathan Dienst

          Hi

           

          Your calendar script looks fine, but it seems that the FactDate values in your fact table are not aligning with the FactDate values in your calendar. You can confirm this by creating a list box for FactDate and checking the values. I expect that you will see "duplicate" values (ie values that look the same but are not actually the same).

           

          What I suspect is that your fact table FactDates have a time component in them, while your calendar FactDates do not. When you load your fact table, use floor() to remove the time component:

           

               FactTable:

               LOAD....

                    Date(Floor(FactDate)) As FactDate,

                    ....

           

           

          HTH

          Jonathan