Associating Master Calendar with Fact tables

[iluilyas]

Hi

I have below fact tables:

1. PurchaseOrders (Date Field: DATE(PURCHS_ORDR_DATE) AS PO_DATE)

2. Contract_Lines (Date Field: DATE(CONTRACT_DATE) AS CONTRACT_DATE)

Below is my calendar:

Temp:

Load min(PO_DATE) as minDate, max(PO_DATE) as maxDate

Resident PurchaseOrders;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + IterNo()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate As PO_DATE,

week(TempDate) As Week,

year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & weekyear(TempDate) as WeekYear,

weekday(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Issue:

I'm showing year filter in filter pane and listing the POs and contracts in table with the year, PO/contract number and total line items.

The issue is, when i select a year from the filter, its filtering only Purchase Orders and not contracts.

What you suggest i should be doing as a standard practice when it comes to year/date filters like this scenario where there are multiple tables involved.

Your help is much appreciated.

Regards, Ilyas

[kaushiknsolanki]

HI,

Check this link for more help.

How to use - Master-Calendar and Date-Values

Regards,

Kaushik Solanki

[iluilyas]

Dear Kaushik

I went through the link and found a similar case of mine where they would like to tie up two fact tables to a single calendar.

They are suggesting to use linked dimension for calendar master.

But due to the lack of clear example, i have difficulty further probing this.

Would you be able to help further with my example?

Regards, Ilyas

[kaushiknsolanki]

For that it would be great if you share the snapshot of the data model (LIst of tables and Fields).

Regards,

Kaushik Solanki

[iluilyas]

Hi Kaushik, attached the script

Regards, Ilyas

[rwunderlich · Accepted solution]

Take a look at this tutorial.

Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

Download the Qlik Sense example by clicking the "S" icon.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[iluilyas]

Thanks Rob. Well understood the concept of Date Link tables and implemented.

Hi Kaushik - got it worked and thanks for your help as well.

Regards, Ilyas

[arvind1494]

good

[kev6brown]

HI, excellent post and I've tried to incorate it in my script however I'm getting "Semantic error", why ?

CALL CalendarFromField('Date1', 'CommonCalendar','');
CALL CalendarFromField('EpisodeDate', 'EpisodeCalendar', 'Episode '); 
CALL CalendarFromField('FoodDate', 'FoodCalendar', 'Food '); 
CALL CalendarFromField('PhysioDate', 'PhysioCalendar', 'Physio '); 
 

Kev