Reply to comment
To achieve the results you are asking about, you will need to do the following:
1) Create a Zip Codes Table where each record is a Zip Code that will be assigned to your projects.
2) In the Zip Codes table, designate a Zip Code (text) field as the key field. (Normally Record ID#)
3) Create a relationship between the Zip Codes table and your Projects table where Zip Codes is the Master table. (When prompted, choose to use an existing field as the reference field in the Projects table. When asked which field to use, select the existing Zip Code field in the Projects table. If there is no such field you will have to create one.)
4) Create a Formula duration field in the Projects table that calculates: Today() - [start date] Name this field “Project Days Elapsed”
5) Create a Formula duration field in the Projects table that calculates [end date] - [start date] Name this field “Project Days Duration”
6) Create a formula text field in the Projects table that calculates: If(IsNull([end date]),"Open", "Closed") Name this field “Status”
7) Create a summary field in Zip Codes table named “Closed: Average Duration.” Use the summary function “Average” where the field being summarized is the field “Project Days Duration.” Use matching criteria summarizing only records where “Status” field is “Closed”
8) Create a lookup field in the Projects table, selecting the “Closed: Average Duration” field from the Zip Codes table as the value field.
9) Create a new report in the Projects table matching only records where the “Status” field is “Open.” Include the following columns in your report:
- Project Days Elapsed
- Zip Code – Closed: Average Duration
You should Sort and Group records by Zip Code, then sort by Days Elapsed.
In the Additional Options section of the report builder, choose the Custom options selection. Check the “Row Colorization” box and enter this formula: If([Proj Days Elapsed]>[Zip Code - Closed: Average Duration], "red")
This report will display all open projects, grouped by Zip Code with columns showing the number of days elapsed since the project began, and the average number of days that it took for other projects to open and close within the same Zip Codes. Any open project in this report that has already been open longer than the average project will be displayed as a red row, highlighting the fact that it is taking longer than average to close this project.