My Master Calendar Does Not Work

[#1]

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.

Can anyone help me with this?

I used the same method before and it worked.

Thank you very much.

PC

[jonathandienst · Accepted solution]

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

[#3]

Hi  I used Date(FactDate) when loading the data in the fact table. Will this do the same? Or this will only change the format?  Thank you  PC

[#4]

Hi  You are right. I try your method and it works.  I gues Date() will only change the formate then.  Thank you very much.  Kind regards,  PC

[#5]

Hi BAIKANG,

just look for this,

MinMax:

LOAD

  Min(FactDate) AS MinDate,

  Max(FactDate) AS MaxDate

RESIDENT FactTable;

here you are using aggr functions at script level so you need to use Group by YourUniqueField.

MinMax:

LOAD

YourUniqueField,

  Min(FactDate) AS MinDate,

  Max(FactDate) AS MaxDate

RESIDENT FactTable group by YourUniqueField;