Common Date Dim

[#1]

Hi All,

I have 4 view/table which are connected to each other. In each view/table, has date field. Please refer to screenshot below:

Now I want to create common date dimension (dates from all 3 tables) and generate report for each table based on selected Year/Month ...  For Ex: # of Activities in 2015, # of project in 2015

I had refered to one the discussion  and implemented the below:

DateLink:
LOAD     

  CUSTOMER_NUMBER
,PROJECT_START_DATE as Date
,'Project' as DateType
RESIDENT Project;
LOAD         

  CUSTOMER_NUMBER
,SALE_DATE as Date
,'Contract' as DateType
RESIDENT Contract;

LOAD   

CUSTOMER_NUMBER
,UPDATED_DATE as Date
,'Activity' as DateType
RESIDENT Activity;

CALL CalendarFromField('Date', 'CommonCalendar', '');

Due to some reasons, results are incorrect. Data is not picked corretly

Can anyone please advise, how this can be acheived?

[sinanozdemir]

I guess you were trying to create a master calendar and this is how I would do:

[swuehl]

I believe your issue is due to the fact that your DateLink table is only linked by CustomerNumber to the other tables.

First, I would suggest to step back and evaluate if a common date field for all three date fields is really what you want.

See also:

Canonical Date

You can try to build a concatenated table consisting of most of the fields of the three tables (some can probably transferred to dimension tables), but this might imply other issues, when you select on specific fields values, thus excluding other rows from your aggregations.

[#4]

Appologies, by mistake mark my question as Assumed Answered. As advised, I tried to create master calendar but Calender is not linked to any tables. What I need/expecting, bsed on Year/Month/Week selection from Master Calender, can able to generate reports for Activities/Contracts/Projects. Common calender should be used as selection/filter acroos all tables.

Date Mapping:

Activity.Updated_date = Calendar.Date

Contract.Sale_date = Calendar.Date

Project.Project_Start_date = Calendar.Date

If I use DERIVE FIELDS FROM SALE_DATE,UPDATED_DATE, PROJECT_START_DATE USING Calendar, then

Results are specific to appropriate tables. My requirements is, in one sheet, I have reports belongs to Activity, Contract, Project, all reports should affect according based on Master Calendar item.

Can you please advise?

[sinanozdemir]

Hi Srinivas,

Let's try this way, change your 'DateLink' model to the below:

And just delete DateLink from the drop table statement.

If I am not wrong, DateLink table will be joined to other tables on Customer_Number field and Calendar on Date field to DateLink table.

Hope this helps