Interval Match accross 2 tables - Too complex for me !!
I currently have a dashboard which looks at rostered staff by hour. I have created an interval match by hour to determine how many staff are rostered by department / day / Hour.
Table 1- Roster:
Roster Date,
Person ID,
Roster Start Time,
Roster End Time,
Staff Level (Qualified / Unqualified),
Department;
From Roster;
This works great, however I now want to bring in the requirements by hour also. However I don't know how to combine the Interval match so that it will reflect the data. The table I have for the requirements is :
Table 2 - Requirements:
Roster Date,
Department,
Staff Level,
Requirement Start Time,
Requirement End Time,
Staff Required;
From Requirements;
The output I need to see is :
Dimensions:
Roster Date
Person ID
Time (Hour)
Staff Level
Expressions:
Count Person ID (Rostered)
Sum Staff Required
If I could somehow link the intervalmatch with each other I think it would work, but I just can't get my head around it.
I realise this will give me duplicate data for the requirements (as it will not be grouped by department), however I can get around this by dividing it by the count of persod ID's.
