Set Analysis on Multiple Tables

[#1]

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.

[Sokkorn]

Hi,

Let try to replace ProjectPhase_Name = {Planning} ==> ProjectPhase_Name = {'Planning'}     (add single quote)

Check the result and get back me

Regards,

Sokkorn

[#3]

Yep, tried single and double quotes but no luck.  It continues to sum all of the hours for the ProjectID.  So the first part of the filtering works it just does not seem to like to evaluate the criteria pulled from the second table.

[Sokkorn]

Hi,

Is it possible to upload your app here? It can be easy to figure the issue.

Regards,

Sokkorn

[#5 · Accepted solution]

Hi,

Use Intervelmatch function to combine both tables Hours_temp, Projectslist Then you will get key between the tables. The table is like this

ProjectID, Projectphase_name, Startdate, endofweek, weeklyhrs, enddate.

once the table is created like abov then your expression will work..

Your expression will not work since there is no join key between the tables..

Hope it helps..

Regards,

Chandra

[#6]

Is vProjectID a string or a number?  How are you assignig the value?

You need a hash sign for the dates. 

Try this.

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

If you post your example, it would be much easier to debug, too. 

Sean

[#7]

Tried the hash on the dates and that did not work.  I am attaching the app as requested.

Note: I removed the ProjectID from the set as this is user selected.  In the application the ProjectID it is Alpha-Numeric and I am using it as the key between the two tables. 

I am also including simplified versions of the spreadsheets.  ACT4Sample contains the weekly hours while the other two contain the phase information for two separate projects.

My inital attempts at the MatchInterval did not work but this is something I will continue to work with as this seems to be a good lead.

Thanks for all the help.

[#8]

This is not perfect but you can select ProjectPhase and shows WorkHours for that phase.

=sum({$<EndOfWeek={">=$(=Min(ProjectPhase_StartDate))<=$(=Max(ProjectPhase_EndDate))"}>} WeeklyHours)

Sean

[#9]

I tried adding the IntervalMatch

IntervalMatch(EndOfWeek, ProjectID) LOAD ProjectPhase_StartDate, ProjectPhase_EndDate, ProjectID Resident ProjectList;

But still not working.  When I look at the data i see that that every EndOfWeek is associated to every ProjectPhase_StartDate and ProjectPhase_EndDate.  It does not appear that it is finding the interval for the date values as the EndOfWeek value is less than, between and greater that the start/end dates.

So I turned each date into a Num# to see what would happed.  The Start/End dates converted to a numeric but the EndOfWeek stays in the 'M/D/YYYY' format.

[#10]

So I resolved the issue converting the date to a number shortly after posing this.

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

But even doing the IntervalMatch on the numeric values of the date field is not working.  The EndOfWeek value keeps joining with each phase name regaless of their start/end date