Formula - Project SV?

[G Lykos]

Greetings! Am interested in calculating the SV contribution of each task
expressed as a percentage. Formula would look something like {Task SV} /
{Project SV} * 100.

Task SV is captured as [SV]. How about Project SV?

Thanks,
George

 

[Jack Dahlgren]

I don't think that will give you very meaningful results...
It is not possible in a custom field formula either as Project SV is not
available to that formula.
You could use a visual basic macro like this to write the value into the
text1 field though:

Sub svthing()
For Each Task In ActiveProject.Tasks
If Not Task Is Nothing Then
Task.Text1 = (Task.SV / ActiveProject.Tasks(1).SV) * 100 & "%"
End If
Next Task
End Sub



-Jack Dahlgren
More about programming MS Project at http://zo-d.com/blog

 

[G Lykos]

Thanks, Jack. Yes, a custom-field formula was the idea, for simplicity and
automation. I had the feeling from having read your posts on this subject
that a project-level SV is not available to a formula but thought I would
ask. A VBA solution is a fall-back alternative.

What specifically is ActiveProject.Tasks(1) in relation to the entire
project - (Project Summary) Task ID 0?

George

I don't think that will give you very meaningful results...
It is not possible in a custom field formula either as Project SV is not
available to that formula.
You could use a visual basic macro like this to write the value into the
text1 field though:

Sub svthing()
For Each Task In ActiveProject.Tasks
If Not Task Is Nothing Then
Task.Text1 = (Task.SV / ActiveProject.Tasks(1).SV) * 100 & "%"
End If
Next Task
End Sub



-Jack Dahlgren
More about programming MS Project at http://zo-d.com/blog

Greetings! Am interested in calculating the SV contribution of each task
expressed as a percentage. Formula would look something like {Task SV} /
{Project SV} * 100.

Task SV is captured as [SV]. How about Project SV?

Thanks,
George

 

[G Lykos]

Jack, tried out the code in MSP 98, and task 1 is task ID 1 (I know, I know,
might seem obvious to the more knowledgeable), and task ID 0 is not
accessible as such, at least via an index. The interest is to determine the
relative contribution to SV of each task. To do so, the project SV is
needed as the reference value. It appears that this is available as
ActiveProject.SV. Also turns out that using Number1 rather than Text1
displays two decimal places naturally, a more compact presentation.

Thanks for the general guidance.

George

Thanks, Jack. Yes, a custom-field formula was the idea, for simplicity and
automation. I had the feeling from having read your posts on this subject
that a project-level SV is not available to a formula but thought I would
ask. A VBA solution is a fall-back alternative.

What specifically is ActiveProject.Tasks(1) in relation to the entire
project - (Project Summary) Task ID 0?

George

I don't think that will give you very meaningful results...
It is not possible in a custom field formula either as Project SV is not
available to that formula.
You could use a visual basic macro like this to write the value into the
text1 field though:

Sub svthing()
For Each Task In ActiveProject.Tasks
If Not Task Is Nothing Then
Task.Text1 = (Task.SV / ActiveProject.Tasks(1).SV) * 100 & "%"
End If
Next Task
End Sub



-Jack Dahlgren
More about programming MS Project at http://zo-d.com/blog

SV}

/

{Project SV} * 100.

Task SV is captured as [SV]. How about Project SV?

Thanks,
George

 

[G Lykos]

One last thing - in thinking more about this, in a normal situation where
there is a mixture of tasks whose SVs are ahead or behind schedule, then the
sum of the absolute value of all task variances is probably needed as a
reference value, and the relative percent contribution of each task will be
positive or negative depending on which way its SV is pulling. I suppose
that means a loop up front to calculate that number.

Jack, tried out the code in MSP 98, and task 1 is task ID 1 (I know, I know,
might seem obvious to the more knowledgeable), and task ID 0 is not
accessible as such, at least via an index. The interest is to determine the
relative contribution to SV of each task. To do so, the project SV is
needed as the reference value. It appears that this is available as
ActiveProject.SV. Also turns out that using Number1 rather than Text1
displays two decimal places naturally, a more compact presentation.

Thanks for the general guidance.

George

Thanks, Jack. Yes, a custom-field formula was the idea, for simplicity and
automation. I had the feeling from having read your posts on this subject
that a project-level SV is not available to a formula but thought I would
ask. A VBA solution is a fall-back alternative.

What specifically is ActiveProject.Tasks(1) in relation to the entire
project - (Project Summary) Task ID 0?

George

I don't think that will give you very meaningful results...
It is not possible in a custom field formula either as Project SV is not
available to that formula.
You could use a visual basic macro like this to write the value into the
text1 field though:

Sub svthing()
For Each Task In ActiveProject.Tasks
If Not Task Is Nothing Then
Task.Text1 = (Task.SV / ActiveProject.Tasks(1).SV) * 100 & "%"
End If
Next Task
End Sub



-Jack Dahlgren
More about programming MS Project at http://zo-d.com/blog



Greetings! Am interested in calculating the SV contribution of each task
expressed as a percentage. Formula would look something like {Task

SV}

/
{Project SV} * 100.

Task SV is captured as [SV]. How about Project SV?

Thanks,
George

 

[Jack Dahlgren]

Sorry,
Was typing without thinking. It should be:
ActiveProject.ProjectSummaryTask.SV

-Jack

Thanks, Jack. Yes, a custom-field formula was the idea, for simplicity
and
automation. I had the feeling from having read your posts on this subject
that a project-level SV is not available to a formula but thought I would
ask. A VBA solution is a fall-back alternative.

What specifically is ActiveProject.Tasks(1) in relation to the entire
project - (Project Summary) Task ID 0?

George

I don't think that will give you very meaningful results...
It is not possible in a custom field formula either as Project SV is not
available to that formula.
You could use a visual basic macro like this to write the value into the
text1 field though:

Sub svthing()
For Each Task In ActiveProject.Tasks
If Not Task Is Nothing Then
Task.Text1 = (Task.SV / ActiveProject.Tasks(1).SV) * 100 & "%"
End If
Next Task
End Sub



-Jack Dahlgren
More about programming MS Project at http://zo-d.com/blog

Greetings! Am interested in calculating the SV contribution of each task
expressed as a percentage. Formula would look something like {Task SV} /
{Project SV} * 100.

Task SV is captured as [SV]. How about Project SV?

Thanks,
George

 

[Jack Dahlgren]

Since the SV of a task can be affected by any number of predecessor tasks
being delayed, what is the point of taking the sum of the absolute value?
For example. Suppose you have a project with 10 sequential tasks. The first
is delayed by 10 days. Calculate the sum of the SV. Now take the same
project, but break the tasks down into 10 subtasks. With the first delayed
by the same 10 days. Calculate the sum of the SV.

The numbers are different by almost an order of magnitude, however, nothing
is fundamentally different about the project.

I'd think first about what you are going to DO if you have this
information - what corrective action will be taken - then think about how to
gather the information. In this regard SV of a single task does not seem to
be that interesting. It does not tell you which task has the problem.

-Jack Dahlgren

One last thing - in thinking more about this, in a normal situation where
there is a mixture of tasks whose SVs are ahead or behind schedule, then
the
sum of the absolute value of all task variances is probably needed as a
reference value, and the relative percent contribution of each task will
be
positive or negative depending on which way its SV is pulling. I suppose
that means a loop up front to calculate that number.

Jack, tried out the code in MSP 98, and task 1 is task ID 1 (I know, I know,
might seem obvious to the more knowledgeable), and task ID 0 is not
accessible as such, at least via an index. The interest is to determine the
relative contribution to SV of each task. To do so, the project SV is
needed as the reference value. It appears that this is available as
ActiveProject.SV. Also turns out that using Number1 rather than Text1
displays two decimal places naturally, a more compact presentation.

Thanks for the general guidance.

George

Thanks, Jack. Yes, a custom-field formula was the idea, for simplicity and
automation. I had the feeling from having read your posts on this subject
that a project-level SV is not available to a formula but thought I would
ask. A VBA solution is a fall-back alternative.

What specifically is ActiveProject.Tasks(1) in relation to the entire
project - (Project Summary) Task ID 0?

George


I don't think that will give you very meaningful results...
It is not possible in a custom field formula either as Project SV is not
available to that formula.
You could use a visual basic macro like this to write the value into the
text1 field though:

Sub svthing()
For Each Task In ActiveProject.Tasks
If Not Task Is Nothing Then
Task.Text1 = (Task.SV / ActiveProject.Tasks(1).SV) * 100 & "%"
End If
Next Task
End Sub



-Jack Dahlgren
More about programming MS Project at http://zo-d.com/blog



Greetings! Am interested in calculating the SV contribution of
each
task
expressed as a percentage. Formula would look something like {Task SV}
/
{Project SV} * 100.

Task SV is captured as [SV]. How about Project SV?

Thanks,
George

 

[G Lykos]

Jack, the interest here, when you have a variety of tasks that result in a
net SPI for the project, is to understand the relative contribution of each
off-schedule task, as measured by schedule variance. The total amount of
variance is the sum of the absolute values of all schedule variances. If
you divide a task's SV by that total and multiple by 100, you have the
percentage impact of that task on SV, and its sign indicates whether it is
pulling the SPI up (work accomplished ahead of baseline plan) or down (the
contrary). You can rank-order this information (pareto analysis). The
calculation is exact. Sum the absolute values of the percentages, you
arrive at 100%, i.e. you have accounted for the relative impact of every bit
of schedule variance in relation to the reference date.

I don't follow your suggestion that this doesn't tell you which task have a
problem. It certainly indicates exactly which tasks are having what impact
on SPI, and will discriminate if you have one task ahead of schedule and one
behind that have a net impact on SPI of zero but are in fact both off plan.
To then look at each task and determine if the problem is self-contained
within that task, or rather is being caused by a linked task, is a next
level of analysis - the why following the what.

Taking your example below, it appears there's a dimension missing. Whether
it's one big task or one hundred small ones strung together, if they model
the same work and use the same assessment of completion status (physical
percent complete, right?), then you'll have the same indication. If they
are serial as suggested, time-now will only intersect one of them, and it
will be reported. Planned work ahead of the reference date has no impact on
SPI, and unaccomplished work behind the reference date is pulling down the
SPI.

Image a mesh where there are a dozen active tasks in a project having 300
tasks that are not at all linearly strung together but rather go in all
different directions. The whole shooting match is a bit off kilter, as
indicated by SPI << 1.0. The reasons for delays may be due to customer
impact, may be self-inflicted, or may be due to other causes (suppliers,
etc.). Then question is then posed - which tasks are having what impact on
the project SPI, and to what general causes is this attributable?

Hope this helps to understand the rationale and interest - thanks for your
thoughts.

George

Since the SV of a task can be affected by any number of predecessor tasks
being delayed, what is the point of taking the sum of the absolute value?
For example. Suppose you have a project with 10 sequential tasks. The first
is delayed by 10 days. Calculate the sum of the SV. Now take the same
project, but break the tasks down into 10 subtasks. With the first delayed
by the same 10 days. Calculate the sum of the SV.

The numbers are different by almost an order of magnitude, however, nothing
is fundamentally different about the project.

I'd think first about what you are going to DO if you have this
information - what corrective action will be taken - then think about how to
gather the information. In this regard SV of a single task does not seem to
be that interesting. It does not tell you which task has the problem.

-Jack Dahlgren

One last thing - in thinking more about this, in a normal situation where
there is a mixture of tasks whose SVs are ahead or behind schedule, then
the
sum of the absolute value of all task variances is probably needed as a
reference value, and the relative percent contribution of each task will
be
positive or negative depending on which way its SV is pulling. I suppose
that means a loop up front to calculate that number.

Jack, tried out the code in MSP 98, and task 1 is task ID 1 (I know, I know,
might seem obvious to the more knowledgeable), and task ID 0 is not
accessible as such, at least via an index. The interest is to

determine
the

relative contribution to SV of each task. To do so, the project SV is
needed as the reference value. It appears that this is available as
ActiveProject.SV. Also turns out that using Number1 rather than Text1
displays two decimal places naturally, a more compact presentation.

Thanks for the general guidance.

George


Thanks, Jack. Yes, a custom-field formula was the idea, for simplicity
and
automation. I had the feeling from having read your posts on this subject
that a project-level SV is not available to a formula but thought I would
ask. A VBA solution is a fall-back alternative.

What specifically is ActiveProject.Tasks(1) in relation to the entire
project - (Project Summary) Task ID 0?

George


I don't think that will give you very meaningful results...
It is not possible in a custom field formula either as Project SV

is
not

available to that formula.
You could use a visual basic macro like this to write the value

into
the

text1 field though:

Sub svthing()
For Each Task In ActiveProject.Tasks
If Not Task Is Nothing Then
Task.Text1 = (Task.SV / ActiveProject.Tasks(1).SV) * 100 & "%"
End If
Next Task
End Sub



-Jack Dahlgren
More about programming MS Project at http://zo-d.com/blog



Greetings! Am interested in calculating the SV contribution of
each
task
expressed as a percentage. Formula would look something like {Task
SV}
/
{Project SV} * 100.

Task SV is captured as [SV]. How about Project SV?

Thanks,
George

 

[G Lykos]

Thanks. In experimenting, had determined that ActiveProject.SV also seems
to work.

Sorry,
Was typing without thinking. It should be:
ActiveProject.ProjectSummaryTask.SV

-Jack

Thanks, Jack. Yes, a custom-field formula was the idea, for simplicity
and
automation. I had the feeling from having read your posts on this subject
that a project-level SV is not available to a formula but thought I would
ask. A VBA solution is a fall-back alternative.

What specifically is ActiveProject.Tasks(1) in relation to the entire
project - (Project Summary) Task ID 0?

George

I don't think that will give you very meaningful results...
It is not possible in a custom field formula either as Project SV is not
available to that formula.
You could use a visual basic macro like this to write the value into the
text1 field though:

Sub svthing()
For Each Task In ActiveProject.Tasks
If Not Task Is Nothing Then
Task.Text1 = (Task.SV / ActiveProject.Tasks(1).SV) * 100 & "%"
End If
Next Task
End Sub



-Jack Dahlgren
More about programming MS Project at http://zo-d.com/blog



Greetings! Am interested in calculating the SV contribution of each task
expressed as a percentage. Formula would look something like {Task

SV}
/

{Project SV} * 100.

Task SV is captured as [SV]. How about Project SV?

Thanks,
George

 

[Jack Dahlgren]

Jack, the interest here, when you have a variety of tasks that result in a
net SPI for the project, is to understand the relative contribution of
each
off-schedule task, as measured by schedule variance. The total amount of
variance is the sum of the absolute values of all schedule variances. If
you divide a task's SV by that total and multiple by 100, you have the
percentage impact of that task on SV, and its sign indicates whether it is
pulling the SPI up (work accomplished ahead of baseline plan) or down (the
contrary).

Ah, but a task can be proceeding at a rate and a cost which match what was
originally planned for the task, but because it was delayed by an earlier
task which completed late it shows negative SV. You aren't pinpointing the
cause, you are pointing at the symptom.

That was my point.

Further, I find it more useful to look at both an early and late curve for
earned value. Delaying a task with a lot of total slack can be a perfectly
reasonable thing to do, but because the baseline is typically based on early
finish, you may be getting SV warnings for tasks which are fairly
inconsequential and which don't affect the critical path. In fact, for cash
flow reasons some might be better off scheduled JIT instead.

But you seem to have some use for it, so don't let me dissuade you.

-Jack

 

[G Lykos]

Symptom vs. root cause is understood - as mentioned, we're first looking for
an answer to the question "what?", to be followed by the "why?". Note that
if a task is being completed at the rate and cost originally anticipated,
but late, then it's late regardless, and is pulling down SPI until completed
late, and perhaps in the process is now interfering with the execution of
other work.

Cheers!