

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Linking to two or more dates
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.


- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Hello,
Please see
Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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.

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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?

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content


- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
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?

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Hi Rob,
I am not able to open the attached qvw file since i am running QV PE.
Earlier i had licensed version so there was no issue opening the doc.
Could you please share the screen shots of the sheets from the app?
It would be great help since your sheets have all details already mentioned.
Thanks & Regards,
Poonam


- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Hi @pnkulwal , The screenshots are included on the download site here:
https://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/
-Rob
- « Previous
- Next »