0 Replies Latest reply: Jan 22, 2014 7:57 AM by Phil Speight RSS

    Interval Match accross 2 tables - Too complex for me !!

    Phil Speight


      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.

       

      Any thoughts?

       

       

      Thanks

       

       

       

      Phil