10 Replies Latest reply: Jan 19, 2013 5:56 PM by cliff_glass RSS

    Set Analysis on Multiple Tables

      Need some assistance getting the syntax right on this expression.  Or maybe I am misusing the set analysis.

       

      I have two tables.  One contains project weekly hours by last day of week by employee for all projects and is a cross table.  The second table contains start and end dates for multiple phases within each project.

       

      My goal is to sum the weekly hours by project by phase.  (Note the dates of the phases do not always start/end on the last day of the week so no key).

       

      Table 1

      Hours_Temp:
      CrossTable(EndOfWeek, WeeklyHours, 4)
      LOAD F1,
      F2,
      F3,
      F4,
      [1/7/2012],
      [1/14/2012],
      [1/21/2012]

            

      ProjectHours:
      NoConcatenate
      LOAD
      F1 as ProjectID,
      F2 as ProjectName,
      F3 as Employee,

           Date#(EndOfWeek,'M/D/YYYY') as EndOfWeek,
      WeeklyHours as WeeklyHours,

           F4 as TotalHours
      Resident Hours_Temp;
      Drop Table Hours_Temp;

       

      Table 2

      ProjectList:
      LOAD
           ProjectID,
           
      ProjectPhase_Name,
           
      Date([Start Date],'M/D/YYYY') as ProjectPhase_StartDate,
           
      Date([End Date],'M/D/YYYY') as ProjectPhase_EndDate,
            …

      The expression that I thought would work is

      Sum( { 1<ProjectID = {$(#vProjectID)}, ProjectPhase_Name = {Planning},EndOfWeek = {">=$(ProjectPhase_StartDate)<=$(ProjectPhase_EndDate)"} > } WeeklyHours)

       

       

      The phase “Planning” would eventually be replaced by a variable like I did for ProjectID.

       

      So far I have tested the syntax by swapping out the field names with variables and can get some things to work by setting the variables to a specific date in the Variable Overview.  But it always breaks when I attempt to limit based on the ProjectPhase_Name.

       

      I may be thinking of things wrong as I cannot even figure out to simply show the ProjectPhase_StartDate in a Text Object.  This may be a follow up question soon.