Apply date to all tables

[darren_dixon]

Hi,

The Main Tables all join by ResourceID and the Master Calendars link to the Main Tables.

At the moment, I have to select Appointment.Year, Unavailability.Year and Staff.Details.Year.

I want to be able select a date which is applied to all tables.

Main Tables

[Appointments]:               [Unavailability]:               [Staff Details]:

ResourceID,                    ResourceID,                    ResourceID,

Appointments.Date,         UnavailabilityDate,           StaffDetails.Date,

Master Calendars

[Appointments-MC]:         [Unavailability-MC]:          [Staff Details-MC]:

Appointments.Date,         UnavailabilityDate,           StaffDetails.Date,

Appointments.Day,          Unavailability.Day,           StaffDetails.Day,

Appointments.Month,       Unavailability.Month,        StaffDetails.Month,

Appointments.Year          Unavailability.Year,          StaffDetails.Year,

Thanks,

Darren

[#2]

Hello Darren,

You can concatenate all your main tables together with a DataType field that will specify the source of each set of data:

Data:

Load 'Appointments' as DataType,

ResourceID,

Appointments.Date as PeriodDate

from Appointments;

concatenate(Data)

Load 'Unavailability' as DataType,

ResourceID,

Unavailability.Date as PeriodDate

from Unavailability;

concatenate(Data)

Load 'StaffDetails' as DataType,

ResourceID,

StaffDetails.Date as PeriodDate

from StaffDetails;

And you'll have one master calendar pointing to PeriodDate.

Then in your expressions just specify the DataType in the set analysis part like that:

count({$<DataType = {'Appointments'}>} field)

Hope this helps

[Gysbert_Wassenaar · Accepted solution]

See Tutorial - Using Common Date Dimensions and Shared Calendars