Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

iluilyas
New 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

Re: Associating Master Calendar with Fact tables

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

7 Replies

Re: Associating Master Calendar with Fact tables

HI,

Check this link for more help.

How to use - Master-Calendar and Date-Values

Regards,

Kaushik Solanki

iluilyas
New Contributor III

Re: Associating Master Calendar with Fact tables

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

Re: Associating Master Calendar with Fact tables

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

Regards,

Kaushik Solanki

iluilyas
New Contributor III

Re: Associating Master Calendar with Fact tables

Hi Kaushik, attached the script

Regards, Ilyas

Re: Associating Master Calendar with Fact tables

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

Re: Associating Master Calendar with Fact tables

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
Valued Contributor

Re: Associating Master Calendar with Fact tables

good