rebuild a traffic light

[remeng]

Hi All,

Well I just moved to a new job and I am having to start from scratch on a traffic light for a new company. Since the link is in the FAQ is dead, can someone help me out with the code? Here is what I have.

Objectives:

1.

​

If field [deadline] is greater than or equal to the finish date = "green" (essentially if the deadline date is the same day as the finish day or after the finish date then the indicator will be green since it isn't past due)


2.

​

if field [Finish] is greater than the [deadline] but no more than 29 days = "yellow" (essentially the traffic light will show yellow if the finish date is 1 - 29 days beyond the scheduled due date.

3.

​

if the [finish] is 30 days or greater than the [deadline] = "red" (essentially if the finish date is over 30 days passed the deadline, the indicator will be red)

Custom Fields:

day to traffic light (Text1)
Traffic Light (Text2)

Standard Fields:

Finish (built in variable) = [Finish]
Deadline (built in variable) = [Deadline]

Indicators:
"green" = green circle
"yellow" = yellow circle
"red" = red circle

Code:

days to traffic light (Text1):

IIf([Deadline]>99999,'No Deadline',DateDiff('d',[Finish],[Deadline]))

Traffic Light (Text2):

Switch([Text1]=([Deadline]>[Finish]),"green",
[Text1]=([Deadline]=[Finish]),"green",
[Text1]=([Deadline]<[Finish]),"yellow",
[Text1]([Deadline]<([Finish]+29)),"yellow",
[Text1]=([Deadline]<([Finish]+30)),"red")

What am I doing wrong?

Thanks,

Mike

 

[JulieInMaine]

In the custom field dialog box, did you select an option under "Calculation for task and group summary rows"? You'll need to enable that to have summary rows calculate. In the graphical indicators there are separate options for summary and project summary rows.

 

[remeng]

Hi Julie,

I was able to set the indicator at the project level. Which option do I use though? It isn't calculating correctly at this time if I use an average. I think it is because of the No Deadlines which is set at -999999999. What can I do to correct it?

Thanks,

Mike

 

[JulieInMaine]

That is up to you. If one of the items under the summary task is past deadline - do you want to show an indicator? You might find "max" works well if that is the case.

 

[remeng]

Well I was hoping to have a indicator at the project level take the average.

 

[JulieInMaine]

The average of what? The days after the deadline? That doesn't seem to make a great deal of sense if a task is running late enough to delay the entire summary task that is more serious than a task that is delayed but will not affect the summary task.

If you have saved a baseline - might you not want to check for summary task finish variance instead?

 

[remeng]

Hi Julie,

Here is what I am trying to determine based on an example of 5 tasks. In short, depending on the overall status, the project level indicator will show an average of what is going on in the project. Also, I think I need to exclude any indicators that state there is no deadline. Currently I think it is throwing off the formula creating the return at the project level traffic light. I hope this helps... Thanks!

Scenario 1: 5 green = project green

Scenario 2: 4 green, 1 yellow = project green

Scenario 3: 3 green, 2 yellow = project green

Scenario 4: 2 green, 3 yellow = project yellow

Scenario 5: 1 green, 4 yellow = project yellow

Scenario 6: 5 yellow = project yellow

Scenario 7: 4 green, 1 red = project green

Scenario 8: 3 green, 2 red = project yellow

Scenario 9: 2 green, 3 red = yellow / red (not sure)

Scenario 10: 1 green, 4 red = project red

Scenario 11: 5 red = project red

Scenario 12: 4 yellow, 1 red = project yellow

Scenario 13: 3 yellow, 2 red = yellow / red (not sure)

Scenario 14: 2 yellow, 3 red = project red

Scenario 15: 1 yellow, 4 red = project red

 

[JulieInMaine]

I don't believe this is going to be possible without code (VBA) and I am not the person to help with that, sorry. The problem as I see it with your scenario is it only takes into account exactly 5 tasks under a summary. What if there are 2 or 3 or 11?

I'd give some further thought about this. I'll ask again - why are you not looking at Finish Variance? You are building a system that is based upon deadlines - and unless you add a deadline to all of your tasks, I don't see this being an effective nor efficient method. Get the sponsor and stakeholders to sign off of the baseline data (start, finish, work, etc) and look at those variances. If you are billing a customer, you may be okay as far as dates - but what if you are over running the work budget by 30%?

 

[remeng]

Julie,

I was just using 5 as an example. There will be a different amount of tasks. This is just a quick snap shot view and isn't intended to be 100% accurate but more like around 85% +. The traffic light isn't tied into any customer info, billables, or anything else. Those are handled outside using other programs.

 

[JulieInMaine]

If there are going to be different amounts of tasks - again I don't see how you are going to accomplish this. Neither code nor formulas can respond to changes on the fly.

 

[remeng]

I just figured it out. Ugg I feel so stupid lol. Because I had the "No Deadline" = -9M and I was using an "average" instead of a "sum" it was forcing the green indicator since the other tasks had no way of averaging out higher than the total (I know it's confusing). Here is the updated formula:

IIf([Deadline]=ProjDateValue("NA"),-365,DateDiff('d',[Deadline],[Finish]))

Since I changed it to use -365 (one year in the past), my sum (no longer the average) of all the task returns can be a positive number or a negative number which allows for all the project level traffic lights. It seems to work. I might have to adjust the no deadline trigger value if it becomes a problem.
----------------------

Final Configurations

Steps

1. Custom Field
2. Type = Number
3. Select Number 1 (field) and rename to Traffic Light
4. Click formula button
5. Enter formula

IIf([Deadline]=ProjDateValue("NA"),-365,DateDiff('d',[Deadline],[Finish]))

6. Press OK
7. Calculation for task and group summary rows set to "Rollup" and the value is "sum"
8. Value to display = Graphical Indicator
9. Click Graphical Indicator button
10. " No Deadline" = -365
"Green" <= 0
"Yellow" is within 1, 30
"Red" >= 31
11. Press OK
12. Press OK
13. Add a new column named Traffic Light
14. Add the Deadline column
15. Set you task Start and Finish date
16. Set your task Deadline date

Your traffic light should now work!

 

[JulieInMaine]

Great. Glad you have it sorted with something that will work for you.