Can a table be linked to two date ranges? help needed

[#1]

Hi everyone,

I have a calendar hierarchy in place which is based around StartDate. However, in one tab of my dashboard I would like a table at the bottom to be linked to both StartDate and EndDate eg if I picked May 2009 it would show all the Tasks that either started or ended in May 2009. Therefore, it could show a task that started in January 2009 but finished in May 2009 or a task that starts in May 2009 but finishes in Sept 2009. I tried to create another calendar hierarchy around EndDate but encountered problems in trying to implement this so that it sovles my problem. does anyone have an idea/method on how this can be done?

I appreciate your help,

bc

[#2]

Could you post an exemple?

[stephencredmond]

Try this.

I am assuming that you have a primary key in your data.

DateLink:
Load
 ID,
 StartDate As DateValue,
 'Start' As DateType
Resident YourDataTable;
Load
 ID,
 EndDate As DateValue,
 'End' As DateType
Resident YourDataTable;

Now you just need to link your calendar to DateValue.

Regards,

Stephen

[Oleg_Troyansky]

if your tasks can span across several months, I guess it's not enough to link the Start Date and the End Date. For example, if the task had started in January and ended in May, - wouldn't you want to show it as "active" in February, March and April?

If you would, - then you need to create a separate Calendar with a separate Date and link it to the Start and End Date using INTERVALMATCH. This way, when a certain Date (or Month) is selected, all the tasks that were active at that date (month) will get selected.

cheers,

Oleg

[#5]

Most of the charts will just be linked to the StartDate whereas the 'raw data' table at the bottom would want to be linked to both to show records starting and ending in a particular month when a date selection is made.

[stephencredmond]

Hi,

Oleg's solution is good but maybe not what you need. My solution gives you an extra field that you can have in a list box so that users can choose to see start dates only, end dates only or both off one calendar. Of course, it is only a good solution if it matches your requirements.

Regards,

Stephen

[#7]

Hi Stephen,

I did try your method but when I loaded it messed up alot of my other charts - basically any using StartDate. We have alot of tabs and many are viewed across time. Any that use startdate were showing no data to display etc.

[#8]

Hi Stephen,

I think the problem I have is my datelink is not matched up to the calendar as yet. Should this be linked on a particular field?

[#9]

Hi Oleg,

Looking at what you have wrote. I have a table named CR which contains (about 20 fields and...) %StartDate and %CREndDate. I have a MasterCalendar table which is created from %StartDate creating fields such as CRMonthYear.

Would this intervalmatch script work? I can;t seem to get it to load, it says it doesn't recognise CR for some reason:

Select * from CR;

Select * from MasterCalendar;

IntervalMatch (CRMonthYear) select %CRStartDate, %CREndDate from CR;

Do you see a problem with this script? As I said before there are many more tabs/tables which have charts using %StartDate etc.

Any more info would be greatly appreciated.

Many thanks,

bc