Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
iluilyas
Contributor III
Contributor III

Associating Master Calendar with Fact tables

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

1 Solution

Accepted Solutions
rwunderlich

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

View solution in original post

8 Replies
kaushiknsolanki

HI,

Check this link for more help.

How to use - Master-Calendar and Date-Values

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
iluilyas
Contributor III
Contributor III
Author

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
iluilyas
Contributor III
Contributor III
Author

Hi Kaushik, attached the script

Regards, Ilyas

rwunderlich

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
Contributor III
Contributor III
Author

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
Specialist
Specialist

good

kev6brown
Partner
Partner

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