Two datefields and one master calendar

[#1]

Hi everybody.

I have one fact table with two datefields, ChangeDate and StockDate, and a master calendar. Currently I'm using one of the datefileds to link to the master calendar, Left(ChangeDateID, 6) as MonthID, and it works fine to use a calendar in my application and choose between different dates.

But, I would like to have two calenders in my applications, one with ChangeDate and another one with StockDate.

Should I create two calendars? Is there anyone that have any example to share?

[sreenivas]

Hi Try this

Data:

Load * Inline

[

StockDate,ChangeDate

08/20/2012,08/21/2012

08/10/2012,08/11/2012

08/15/2012,08/14/2012

07/14/2012,07/21/2012

08/13/2012,08/19/2012

08/12/2012,08/18/2012

08/11/2012,08/17/2012

08/10/2012,08/16/2012

];

CommonDate:

LOAD StockDate as DAte Resident Data;

Concatenate

LOAD ChangeDate as DAte Resident Data;

[#3]

Hi,

I think you want to have a single calendar which should reflect the data for the other two dates aswell.

So, you can create a link table between your fact table and the master calendar.

This link table will inclued both the dates concatenated or joned as a single name filed called Date which will get link to master calendar.Also you need to create a synthetic key with change date and stock date which will link the fact table and link table.

[masha-ecraft]

Hi,

Add a field to your fact table:

MonthName(ChangeDate) & '/' & MonthName(StockDate) as CalendarKey

Then create a new master calendar:

Calendar:

LOAD

     MonthName(ChangeDate) & '/' & MonthName(StockDate) as CalendarKey,

     'Change Date' as DateType,

     MonthName(ChangeDate) as Month

Resident FactTable;

Concatenate (Calendar)

LOAD

LOAD

     MonthName(ChangeDate) & '/' & MonthName(StockDate) as CalendarKey,

     Stock Date' as DateType,

     MonthName(StockDate) as Month

Resident FactTable;

You can create a list box for DateType field so the user can select which date he is using.

/Masha

[#5]

Do you have any example on have you suggest to solve the issue?

I've attached a example on my issue.

[#6]

Hi,

Please find the attached application.Reload it at ur end as  i was not having the excel file required to reload.

[sebastiandperei]

Hi,

Note that you are linking about months, and leave out days.

If you want to have two different calendars, and link by day, the script could be:

Contract:

LOAD ChangeDateID,

     StockDateID

FROM

(ooxml, embedded labels, table is TB02_20120823_120900);

Stock_Calendar:

NoConcatenate

Load Distinct

     StockDateID,

     MakeDate(Left(StockDateID,4), num(Mid(StockDateID,5,2)),Right(StockDateID,2)) as ST_Date

Resident Contract;

Left Join

Load

     ST_Date,

     Month(ST_Date) as ST_Month,

     Year(ST_Date) as ST_Year,

     Day(ST_Date) as ST_Year,

     Ceil(Month(ST_Date)/4) as ST_Quarter

Resident Stock_Calendar;

Change_Calendar:

Load Distinct

     ChangeDateID,

     MakeDate(Left(ChangeDateID,4), num(Mid(ChangeDateID,5,2)),Right(ChangeDateID,2)) as CH_Date

Resident Contract;

Left Join

Load

     CH_Date,

     Month(CH_Date) as CH_Month,

     Year(CH_Date) as CH_Year,

     Day(CH_Date) as CH_Year,

     Ceil(Month(CH_Date)/4) as CH_Quarter

Resident Stock_Calendar;

Now, you will get the different fields for each date (Stock and Change)

Note that when you will select one date, the other dates will be filter to the registers with the date you have selected.

[#8]

It still doesn't working successfully. And how do I manage to use two calendar objects with different fields?

[#9]

See attached files.

[#10]

The functionallity I'm looking for is like attached qvw. But this is maybe not the best way to do it.