topic Re: My Master Calendar Does Not Work in QlikView

My Master Calendar Does Not Work

Wed, 09 Jul 2014 06:42:56 GMT

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.

Re: My Master Calendar Does Not Work

jonathandienst — Wed, 09 Jul 2014 06:56:34 GMT

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

Re: My Master Calendar Does Not Work

Wed, 09 Jul 2014 07:00:29 GMT

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

Re: My Master Calendar Does Not Work

Wed, 09 Jul 2014 07:03:20 GMT

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

Re: My Master Calendar Does Not Work

Wed, 09 Jul 2014 07:13:07 GMT

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;