Linking to two or more dates

[rwunderlich]

This tutorial presents a script pattern for assigning time dimensions to multiple fact dates in a data model. It answers the commonly asked Forum question "how do I link to two dates"?

The pattern will demonstrate how to link all fact dates to a common calendar as well as using separate calendars for each fact date.

[rwunderlich]

Hello,

Please see

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

[Cof59]

Hello @rwunderlich , 

Please excuse my poor level of English.

I read your explanation attentively but I do not manage to apply it to my own case.  I tried to explain my data model and my objectif as well as possible.

I tried to explain my data model and my goal as well as possible.

Could you give me some advice?  Thank you in advance.

[Kejti]

Hello @rwunderlich , thank you very much for this post.

In my case I have one table 'Partners' in which there are 2 diff field dates:

Completion Date and Enrollment Date.

I've created a single calendar for both of them, LinkTable and CommonCalendar - all looks good in data model viewer. Data work proper now for - commonDate, EnrollDate and CompletDate - but I am not able to see any data for month, year, quarter... It seems like OrderID is not connected to them. But it is do the date.... Not sure how is that possible if model looks good:

Any ideas what went wrong? 

[Kejti]

[rwunderlich]

When you click on Enrol_Month in the Data Model Viewer do you have any values? What about the subset ratios for Enrollment Date in the EnrollCalendar? Is it 100%?

-Rob

[krmvacar]

Hi @rwunderlich 

ı have a two tables and 2 dates but Canocıcal Date ıs not workıng 

TEST1:
LOAD
KEYFLDPERNUM,
 KEYFLDDATE2,
TARFARK ;

TEST2:

LOAD
KEYFLDPERNUM,
 CHANGEDATE,
RESULT;

tmpDateBridge:
Load
KEYFLDPERNUM,
CHANGEDATE as KEYFLDDATE,
'Order' as DateType
Resident TEST1;

Load
KEYFLDPERNUM,
KEYFLDDATE2 as KEYFLDDATE,
'Shipped' as DateType
Resident TEST2;

NoConcatenate
DateBridge:
Load * Resident tmpDateBridge where IsNull(KEYFLDDATE) = 0;

Drop Table tmpDateBridge;

MasterCalendar:

Load
KEYFLDDATE AS KEYFLDDATE,
Num#(Date(KEYFLDDATE, 'YYYYMMDD')) AS KEYFLDDATENUM,
Week(KEYFLDDATE) AS Week,
Year(KEYFLDDATE) AS Year,
Month(KEYFLDDATE) AS Month,
Ceil(Month (KEYFLDDATE)/1) AS MonthNum,
Day(KEYFLDDATE) AS Day,
Weekday (KEYFLDDATE) AS WeekDay,
'Q' & Ceil(Month (KEYFLDDATE)/3) AS Quarter,
Year(KEYFLDDATE)&'-Q' & Ceil(Month (KEYFLDDATE)/3) AS YearQuarter,
'HY' & Ceil(Month (KEYFLDDATE)/6) AS HalfYear,
Year(KEYFLDDATE)&'-HY' & Ceil(Month (KEYFLDDATE)/6) AS YearHalfYear,
Date(Monthstart(KEYFLDDATE), 'MM-YYYY') AS MonthYear,
Date(Monthstart(KEYFLDDATE), 'YYYYMM') AS YearMonth,
Week(KEYFLDDATE) & '-' & Year (KEYFLDDATE) AS WeekYear;
load
Date(MinKEYFLDate+iterno() -1) as KEYFLDDATE
While MinKEYFLDate+iterno()-1 <=MaxKEYFLDate;

Load
Date(Min(FieldValue('KEYFLDDATE',Recno()))) as MinKEYFLDate,
Date(Max(FieldValue('KEYFLDDATE',Recno()))) as MaxKEYFLDate
Autogenerate FieldValueCount('KEYFLDDATE');

My measures:

Sum( {$<DateType={'Order'}>} RESULT)

Sum( {$<DateType={'Shipped'}>} TARFARK)

Can you help me please?