Stoplight Graphical Indicator Test

[Sara Miller]

This is a follow-up to my question about the formula for customizing a field
(thank you John for your help!).

Now that the formula is working and returning a numeric value in the Number1
field, I can't seem to get the indicators to work correctly. I am definitely
NOT a programmer so this may just be a simple logic issue.

Here are the tests I've entered for the Number1 field:
- is greater than 0.00 green button
- is less than or equal to (15.00) yellow button
- is less than or equal to (16.00) red button

The results of my three test tasks are:
- value 0 shows blank
- value -14 shows blank
- value -365 shows yellow

What am I doing wrong?

Thanks,
Sara

 

[Jim Aksel]

My Assumption: (15.00) means negative 15 or -15.00.

Negative numbers are reversed in logic. A value of -20 is actually less
than a value of -15. Less than means "to the left of on a number line"

So, what I see in your testing is there is a hole in your logic from 0 down
to -15. In that range, there are no values defined.

The value of 0 is also not defined to your logic ... I can be green only if
greater than 0.

If you are using the graphical indicators, Project will take the first value
that passes the test. Since -365 is less then -15 the test is passed, the
indicator is yellow and the test exits.

Try stacking it up this way (in reverse)
<=-16 red
<=-15 yellow
<=0 (pink)???

0 green

See if that order helps you some, and fills in the gap.
--
If this post was helpful, please consider rating it.

Jim
It''s software; it''s not allowed to win.

Visit http://project.mvps.org/ for FAQs and more information
about Microsoft Project

 

[John]

This is a follow-up to my question about the formula for customizing a field
(thank you John for your help!).

Now that the formula is working and returning a numeric value in the Number1
field, I can't seem to get the indicators to work correctly. I am definitely
NOT a programmer so this may just be a simple logic issue.

Here are the tests I've entered for the Number1 field:
- is greater than 0.00 green button
- is less than or equal to (15.00) yellow button
- is less than or equal to (16.00) red button

The results of my three test tasks are:
- value 0 shows blank
- value -14 shows blank
- value -365 shows yellow

What am I doing wrong?

Thanks,
Sara

Sara,
Are you using the same formula (i.e. DateDiff("d",[finish],[date1]) to
calculate the values for Number1? If not, what formula are you using and
what are the values of the arguments (i.e. finish, date1) for your three
test tasks?

The logic of your indicators isn't quite what you want. It basically
says that everything greater than 0 will be green. It doesn't matter if
the value is less than 15 or 16 because once the value is evaluated for
the first test (i.e. greater than 0), the value is true and that
indicator will be applied. So what exactly are you trying to show with
the indicators?

John
Project MVP

 

[Sara Miller]

Thanks Jim. I tried your suggestion:

Try stacking it up this way (in reverse)
<=-16 red
<=-15 yellow
<=0 (pink)???

and got the following results:
- value 0 shows pink
- value -14 shows pink
- value -365 shows red

???
Thanks for your help,
Sara

 

[Sara Miller]

Hi John, yes this is the same formula and the data in Number1 is showing the
appropriate number (0, -14 and -365). The intent of the formula is to
determine the "gap" between when a task is finished [Finish] and when it is
"required" [Date1]; this date will be manually entered.

I realized after I wrote my question that I actually have 4 conditions that
I need to allow for. Here is what I want to happen in the Number1 field:
- any value equal to or less than -16 displays a red button (-16, -17,
etc.)
- any value between -1 and -15 inclusive displays a yellow button
- any value greater than or equal to zero displays a green button
- any value other than those above is blank (not all Date1 fields will
have data).

Thanks!
Sara

This is a follow-up to my question about the formula for customizing a field
(thank you John for your help!).

Now that the formula is working and returning a numeric value in the Number1
field, I can't seem to get the indicators to work correctly. I am definitely
NOT a programmer so this may just be a simple logic issue.

Here are the tests I've entered for the Number1 field:
- is greater than 0.00 green button
- is less than or equal to (15.00) yellow button
- is less than or equal to (16.00) red button

The results of my three test tasks are:
- value 0 shows blank
- value -14 shows blank
- value -365 shows yellow

What am I doing wrong?

Thanks,
Sara

Sara,
Are you using the same formula (i.e. DateDiff("d",[finish],[date1]) to
calculate the values for Number1? If not, what formula are you using and
what are the values of the arguments (i.e. finish, date1) for your three
test tasks?

The logic of your indicators isn't quite what you want. It basically
says that everything greater than 0 will be green. It doesn't matter if
the value is less than 15 or 16 because once the value is evaluated for
the first test (i.e. greater than 0), the value is true and that
indicator will be applied. So what exactly are you trying to show with
the indicators?

John
Project MVP

 

[John]

Hi John, yes this is the same formula and the data in Number1 is showing the
appropriate number (0, -14 and -365). The intent of the formula is to
determine the "gap" between when a task is finished [Finish] and when it is
"required" [Date1]; this date will be manually entered.

I realized after I wrote my question that I actually have 4 conditions that
I need to allow for. Here is what I want to happen in the Number1 field:
- any value equal to or less than -16 displays a red button (-16, -17,
etc.)
- any value between -1 and -15 inclusive displays a yellow button
- any value greater than or equal to zero displays a green button
- any value other than those above is blank (not all Date1 fields will
have data).

Thanks!
Sara

Sara,
Since you have a range of values for the yellow button, you can't get
there from here using a simple formula. Based on your requirements, I
would use the following complex formula in the Number1 field:
IIf(datediff("d",[Finish],[Date1])>=0,1,IIf(datediff("d",[Finish],[Date1]
)<0 And
datediff("d",[Finish],[Date1])>-16,2,IIf(datediff("d",[Finish],[Date1])<-
15,3,4)))

Then set up the graphical indicators as follows:
1=green
2=yellow
3=red
4=nothing (blank)

Oh, one more consideration. The formula you are using will give the
difference in elapsed (i.e. calendar) days. Is that what you want or do
you perhaps want difference in working days? If you really want the
difference in working days, then you will need to modify the formula by
using the ProjDateDiff function rather than the DatDiff function. Be
advised that the syntax and arguments are different and the ProjDateDiff
function gives the difference in minutes so you will have to divide the
result by 480, if you are using a standard 8 hour work day.

Hope this helps.
John
Project MVP

This is a follow-up to my question about the formula for customizing a
field
(thank you John for your help!).

Now that the formula is working and returning a numeric value in the
Number1
field, I can't seem to get the indicators to work correctly. I am
definitely
NOT a programmer so this may just be a simple logic issue.

Here are the tests I've entered for the Number1 field:
- is greater than 0.00 green button
- is less than or equal to (15.00) yellow button
- is less than or equal to (16.00) red button

The results of my three test tasks are:
- value 0 shows blank
- value -14 shows blank
- value -365 shows yellow

What am I doing wrong?

Thanks,
Sara

Sara,
Are you using the same formula (i.e. DateDiff("d",[finish],[date1]) to
calculate the values for Number1? If not, what formula are you using and
what are the values of the arguments (i.e. finish, date1) for your three
test tasks?

The logic of your indicators isn't quite what you want. It basically
says that everything greater than 0 will be green. It doesn't matter if
the value is less than 15 or 16 because once the value is evaluated for
the first test (i.e. greater than 0), the value is true and that
indicator will be applied. So what exactly are you trying to show with
the indicators?

John
Project MVP

 

[Sara Miller]

That did it! Thanks again...

BTW, I just found these discussion groups recently & they have been
invaluable! Even when I'm searching for info on one issue I find great
tidbits on other issues that have really helped me do my job. Thanks to
everyone!

Sara

Hi John, yes this is the same formula and the data in Number1 is showing the
appropriate number (0, -14 and -365). The intent of the formula is to
determine the "gap" between when a task is finished [Finish] and when it is
"required" [Date1]; this date will be manually entered.

I realized after I wrote my question that I actually have 4 conditions that
I need to allow for. Here is what I want to happen in the Number1 field:
- any value equal to or less than -16 displays a red button (-16, -17,
etc.)
- any value between -1 and -15 inclusive displays a yellow button
- any value greater than or equal to zero displays a green button
- any value other than those above is blank (not all Date1 fields will
have data).

Thanks!
Sara

Sara,
Since you have a range of values for the yellow button, you can't get
there from here using a simple formula. Based on your requirements, I
would use the following complex formula in the Number1 field:
IIf(datediff("d",[Finish],[Date1])>=0,1,IIf(datediff("d",[Finish],[Date1]
)<0 And
datediff("d",[Finish],[Date1])>-16,2,IIf(datediff("d",[Finish],[Date1])<-
15,3,4)))

Then set up the graphical indicators as follows:
1=green
2=yellow
3=red
4=nothing (blank)

Oh, one more consideration. The formula you are using will give the
difference in elapsed (i.e. calendar) days. Is that what you want or do
you perhaps want difference in working days? If you really want the
difference in working days, then you will need to modify the formula by
using the ProjDateDiff function rather than the DatDiff function. Be
advised that the syntax and arguments are different and the ProjDateDiff
function gives the difference in minutes so you will have to divide the
result by 480, if you are using a standard 8 hour work day.

Hope this helps.
John
Project MVP

This is a follow-up to my question about the formula for customizing a
field
(thank you John for your help!).

Now that the formula is working and returning a numeric value in the
Number1
field, I can't seem to get the indicators to work correctly. I am
definitely
NOT a programmer so this may just be a simple logic issue.

Here are the tests I've entered for the Number1 field:
- is greater than 0.00 green button
- is less than or equal to (15.00) yellow button
- is less than or equal to (16.00) red button

The results of my three test tasks are:
- value 0 shows blank
- value -14 shows blank
- value -365 shows yellow

What am I doing wrong?

Thanks,
Sara

Sara,
Are you using the same formula (i.e. DateDiff("d",[finish],[date1]) to
calculate the values for Number1? If not, what formula are you using and
what are the values of the arguments (i.e. finish, date1) for your three
test tasks?

The logic of your indicators isn't quite what you want. It basically
says that everything greater than 0 will be green. It doesn't matter if
the value is less than 15 or 16 because once the value is evaluated for
the first test (i.e. greater than 0), the value is true and that
indicator will be applied. So what exactly are you trying to show with
the indicators?

John
Project MVP

 

[John]

That did it! Thanks again...

BTW, I just found these discussion groups recently & they have been
invaluable! Even when I'm searching for info on one issue I find great
tidbits on other issues that have really helped me do my job. Thanks to
everyone!

Sara

Sara,
You're welcome and thanks for the feedback.
John

Hi John, yes this is the same formula and the data in Number1 is showing
the
appropriate number (0, -14 and -365). The intent of the formula is to
determine the "gap" between when a task is finished [Finish] and when it
is
"required" [Date1]; this date will be manually entered.

I realized after I wrote my question that I actually have 4 conditions
that
I need to allow for. Here is what I want to happen in the Number1 field:
- any value equal to or less than -16 displays a red button (-16,
-17,
etc.)
- any value between -1 and -15 inclusive displays a yellow button
- any value greater than or equal to zero displays a green button
- any value other than those above is blank (not all Date1 fields
will
have data).

Thanks!
Sara

Sara,
Since you have a range of values for the yellow button, you can't get
there from here using a simple formula. Based on your requirements, I
would use the following complex formula in the Number1 field:
IIf(datediff("d",[Finish],[Date1])>=0,1,IIf(datediff("d",[Finish],[Date1]
)<0 And
datediff("d",[Finish],[Date1])>-16,2,IIf(datediff("d",[Finish],[Date1])<-
15,3,4)))

Then set up the graphical indicators as follows:
1=green
2=yellow
3=red
4=nothing (blank)

Oh, one more consideration. The formula you are using will give the
difference in elapsed (i.e. calendar) days. Is that what you want or do
you perhaps want difference in working days? If you really want the
difference in working days, then you will need to modify the formula by
using the ProjDateDiff function rather than the DatDiff function. Be
advised that the syntax and arguments are different and the ProjDateDiff
function gives the difference in minutes so you will have to divide the
result by 480, if you are using a standard 8 hour work day.

Hope this helps.
John
Project MVP

:

This is a follow-up to my question about the formula for customizing
a
field
(thank you John for your help!).

Now that the formula is working and returning a numeric value in the
Number1
field, I can't seem to get the indicators to work correctly. I am
definitely
NOT a programmer so this may just be a simple logic issue.

Here are the tests I've entered for the Number1 field:
- is greater than 0.00 green button
- is less than or equal to (15.00) yellow button
- is less than or equal to (16.00) red button

The results of my three test tasks are:
- value 0 shows blank
- value -14 shows blank
- value -365 shows yellow

What am I doing wrong?

Thanks,
Sara

Sara,
Are you using the same formula (i.e. DateDiff("d",[finish],[date1]) to
calculate the values for Number1? If not, what formula are you using
and
what are the values of the arguments (i.e. finish, date1) for your
three
test tasks?

The logic of your indicators isn't quite what you want. It basically
says that everything greater than 0 will be green. It doesn't matter if
the value is less than 15 or 16 because once the value is evaluated for
the first test (i.e. greater than 0), the value is true and that
indicator will be applied. So what exactly are you trying to show with
the indicators?

John
Project MVP