Earned Value Management 1

[YANKRAY]

Using CR10.

I am trying to create a Report that will show the Daily Burn Rates for individual work orders so I can group by the Dates to get a Daily Burn Rate for Each date represented by all the work orders.

For Example:

WORK ORDER TOTAL HRS START DATE FIN DATE DUR HRS PER DAY

WO 1 10 2/1/2011 2/2/2011 2 (days) 5
WO 2 20 2/1/2011 2/3/2011 3 6.6
WO 3 40 2/2/2011 2/4/2011 2 13.4

I have calculated fields for Duration (Fin - Start.
and Hrs per day (Total Hrs / Duration).

I am trying to calculate Hours per Day (Burn Rate) over the duration of all three work orders (2/1/2011 to 2/4/2011).
Then to Group this by each day to see the following result.

2/1/2011 11.6
2/2/2011 25.0
2/3/2011 20.0
2/4/2011 13.4

This should total to the sum of the TOTAL HRS COLUMN which is 70.0.

How can I create the desired result on a daily basis?

Thanks,
Ray

 

[YANKRAY]

One thing I forgot:

Weekends should not be counted. Just Weekdays.

 

[lbass]

I think you would have to add a table that contains all dates (create in Excel, e.g.), and then link it to the main table by using >= join linked to the start date and a <= join for the end date. This would add one record per day, which would allow you to group on the newtable.date and use the same value field multiple times. You could add a selection formula that removes weekends:

not(dayofweek({Excel.date}) in [1,7])

-LB

 

[YANKRAY]

LB,

I have applied the table in Excel with the links you stated.

I am only getting one record per work order and a couple of them are blank (null).

Not sure how to show you what I have done;

Created a table in Excel (one table with dates from 1/1/2010
through 12/31/2014

Joined the Start Date for the Work order >= the new tabel Date field.
Joined the Finish Date for the Work order <- the new table Date
field.

Grouped by Work Order just to see the records per work order and there was only one record with one Date.

If there is anything else I need to tell you, let me know.

Thanks,
Ray

 

[YANKRAY]

LB,

The Blanks (null) are on the work orders that spread across more than one day.

The ones with the Date are the one day work orders so I don't know if I am picking up the Start Date or the Finish Date.

Ray

 

[lbass]

You need to use the NewTable as your lead table and link FROM the {NewTable.date} TO the start date (inner join, >= join type) and TO the Finish Date (inner join, <= join type). Then add the selection formula that excludes weekends on the {NewTable.date}. Then you should insert a group (on change of day) on {NewTable.date}. I just tried this, and it resulted in one row per date for each date between the start and finish date per work order--which is what you need to happen so that you can summarize hours for each date.

-LB

 

[YANKRAY]

LB

I found out that our system had a view created that was just dates. I guess it is used in other programming.

I used this view and everything worked perfectly. It was a little slow because of the large date range in the view, but it is working ok.

The issue I have with weekends is that I do not just want to exclude them in the result, but want the record selection to skip over them if they are used.

For Example:

If a work order runs the date range of 3/3 to 3/6, I get a line for;

3/3 Thur
3/4 Fri
3/5 Sat
3/6 Sun

What I want in the exclusion of weekends is to see lines for
3/3 Thur
3/4 Fri
3/7 Mon
3/8 Tue

This may not even be possible since the Start Date is 3/3 and the Finish Date is 3/6.

The Scheduling system we have developed will not Start a Work Order on Sat or Sun, but the Duration (Start - Finish) will let a Finish Date be a Sat or Sun.

Is is possible to get the record selection in Crystal to bypass the Sat/Sun and extend the results out?

thanks,
Ray

 

[lbass]

I think you need to train people not to add Sat and Sun as finish dates.

Adjusting dates would mess up the groupings, unless you convert the finish dates before linking. Maybe you could do this in a view and then link the view to the spreadsheet dates. Not sure.

if dayofweek({table.finishdate}) in [1,7] then
{table.finishdate}+2 else
{table.finishdate}

-LB

 

[YANKRAY]

I pretty much felt the same way.
I am going to approach it withing the scheduling tool to see if it will add duration past Sat or Sun. I think it will work.

Thanks again for your help with the date ranges. I now have a very effective scheduling tool.

Ray