Skip to main content
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

Linking to two or more dates

cancel
Showing results for 
Search instead for 
Did you mean: 
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Linking to two or more dates

Last Update:

Feb 7, 2014 2:44:13 PM

Updated By:

rwunderlich

Created date:

Feb 7, 2014 2:44:13 PM

Attachments

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.

Labels (2)
Comments
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Hello,

Please see

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

0 Likes
Cof59
Contributor II
Contributor II

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.Capture01.JPG

Capture02.JPG

Capture03.JPG

 

 

0 Likes
Kejti
Partner - Contributor II
Partner - Contributor II

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:

Kejti_0-1657713298723.png

Any ideas what went wrong? 

0 Likes
Kejti
Partner - Contributor II
Partner - Contributor II

Kejti_0-1657714513538.png

 

0 Likes
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

0 Likes
krmvacar
Creator II
Creator II

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)

krmvacar_0-1662201749865.png

 

Can you help me please?

0 Likes
Version history
Last update:
‎2014-02-07 02:44 PM
Updated by: