Proximity to Goal variable
Lately HUD has been encouraging CoC's to set objectives designed to end homelessness. I worked with the CoC committee tasked with setting the objectives to create a report that could show how each project was doing in relation to these goals. That report helped the committee get baseline data to know where projects were in relation to the various goals, and will continue to help with monitoring progress.
Naturally, projects like to know where they are in relation to the goals as well, so I designed a companion report for the projects themselves, complete with details like Client IDs and other relevant data. This report's best feature (in my opinion!) is the Proximity to Goal variable. Not because it was hard to code, but because it really adds a quick way for projects to see where they are succeeding and where they could stand to improve. When a project runs this report, it looks at the Program Type Code of the project selected, and then returns how close the project was to hitting the goal set for that program type. It will say "Achieved" if the goal is met, and if the goal is missed by say 5%, it will say "-5%". If the project has too much missing data but still meets the goal, the committee decided that the goal should not be considered to be met because of data quality. So when this happens, the variable returns "Not Met Because of Data Quality." Since I had to design it for any project type, every tab does not necessarily apply to every project type. So the variable also returns "N/A" if that tab is not applicable to that project type. Here are some screenshots:
Achieved goal.
In the above image, you can see the goal for shelters was 30% of clients and the project got 62.2%, so the goal is "Achieved". Below this is the client detail.
Missed the goal.
This is that annoying HUD goal that everyone hates. Still, it is a good example of a project missing a goal. You can see here where the goal is 80% of clients and the project only has 61.4%. In the detail it is easy to see why people hate this goal- two of the clients just entered the program and so have not had a chance to rack up a good number of days yet.
Goal met, but since the data quality is not good enough, it will not count.
Even though the project (barely) hit the goal, it will be considered "not met" because the data quality is not good enough.
Anyway, the code! Here is the code for one of these variables. They are all of the same general structure, so I will not post all of them here, but you can get the general idea at least:
First, I am looking for what the data quality percentage is. If it's greater than 2%, the variable will simply return "Not Met Because of Data Quality".
1=If([MissingIncomeCountLeaversOnly] / [AdultLeaverCount] > 0.02; "Not Met Because of Data Quality";
Next I'm looking to see if the project met the goal laid out for the program type of the agency that the report is being run on. In this case, it's checking to see that any ES, PSH, or SH projects hit 15%, as dictated in the plan. If so, the variable says "Achieved".
1If([ProgramTypeCode] InList ("Emergency Shelter (HUD)" ;"Permanent supportive housing (HUD)" ;
2 "Safe Haven (HUD)") And [EarnedIncomeatExit] / [AdultLeaverCount] >= 0.15;
3 "Achieved";
Here, I'm telling it what to do when the project has not met the goal. It is subtracting the goal from the reality so that if a project misses the goal by 4%, it will return "-4%" rounded to the nearest percentage.
1If([ProgramTypeCode] InList ("Emergency Shelter (HUD)";"Permanent supportive housing (HUD)";"Safe Haven (HUD)") And
2 [EarnedIncomeatExit]/[AdultLeaverCount] < 0.15;
3 FormatNumber([EarnedIncomeatExit]/[AdultLeaverCount] - 0.15;
4 "#,##0.[%]%");
And here I'm accounting for the fact that zeroes come through as nulls. So if a project had 0 out of 15 clients meet the goal, it will come through as "null" out of 15 and the above If statement will not catch it. So I have to tell it to look for instances where the number of clients where they meet the goal is null and the number of possible clients is greater than 0, to just return -15% since they are 15% away from meeting the goal.
1If([ProgramTypeCode] InList ("Emergency Shelter (HUD)";"Permanent supportive housing (HUD)";"Safe Haven (HUD)") And [AdultLeaverCount] > 0 And IsNull ([EarnedIncomeatExit]); "-15%";
The next If statements are generally of the same format and purpose, only with different project types and goals.
1If([ProgramTypeCode] = "Rapid Re-Housing (HUD)" And [EarnedIncomeatExit] / [AdultLeaverCount] >= 0.5;
2 "Achieved";
3 If([ProgramTypeCode] ="Rapid Re-Housing (HUD)" And [EarnedIncomeatExit] / [AdultLeaverCount] < 0.5;
4 FormatNumber ([EarnedIncomeatExit] /[AdultLeaverCount] - 0.5;
5 "#,##0.[%]%");
6 If([ProgramTypeCode] ="Rapid Re-Housing (HUD)" And IsNull ([EarnedIncomeatExit]); "-50%";
7 If([ProgramTypeCode] InList("Transitional housing (HUD)" ;"Services only program (HUD)") And ([EarnedIncomeatExit] / [AdultLeaverCount] >= 0.4);
8 "Achieved";
9 If(([ProgramTypeCode] ="Transitional housing (HUD)" Or
10 [Program Type Code] ="Services only program (HUD)") And
11 [EarnedIncomeatExit] / [AdultLeaverCount] < 0.4;
12 FormatNumber ([EarnedIncomeatExit] /[AdultLeaverCount] - 0.4;
13 "#,##0.[%]%");
14 If(([ProgramTypeCode] ="Transitional housing (HUD)" Or
15 [ProgramTypeCode] ="Services only program (HUD)") And
16 [AdultLeaverCount] > 0 And IsNull ([EarnedIncomeatExit]);
17 "-40%";
If anything comes through and none of the above criteria are met, it will simply return "N/A".
1"N/A" ))))))))))
Below is the full variable, all together.
1=If([MissingIncomeCountLeaversOnly] / [AdultLeaverCount] > 0.02;
2 "Not Met Because of Data Quality";
3 If([ProgramTypeCode] InList ("Emergency Shelter (HUD)" ;
4 "Permanent supportive housing (HUD)" ;"Safe Haven (HUD)") And
5 [EarnedIncomeatExit] / [AdultLeaverCount] >= 0.15;
6 "Achieved";
7 If([ProgramTypeCode] InList("Emergency Shelter (HUD)" ;
8 "Permanent supportive housing (HUD)";"Safe Haven (HUD)") And
9 [EarnedIncomeatExit]/[AdultLeaverCount] < 0.15;
10 FormatNumber ([EarnedIncomeatExit]/[AdultLeaverCount] - 0.15;
11 "#,##0.[%]%");
12 If([ProgramTypeCode] InList ("Emergency Shelter (HUD)";
13 "Permanent supportive housing (HUD)";"Safe Haven (HUD)") And
14 [AdultLeaverCount] > 0 And IsNull ([EarnedIncomeatExit]);
15 "-15%";
16 If([ProgramTypeCode] ="Rapid Re-Housing (HUD)" And
17 [EarnedIncomeatExit] / [AdultLeaverCount] >= 0.5;
18 "Achieved";
19 If([ProgramTypeCode] ="Rapid Re-Housing (HUD)" And
20 [EarnedIncomeatExit] / [AdultLeaverCount] < 0.5;
21 FormatNumber ([EarnedIncomeatExit] /[AdultLeaverCount] - 0.5;
22 "#,##0.[%]%");
23 If([ProgramTypeCode] ="Rapid Re-Housing (HUD)" And
24 IsNull ([EarnedIncomeatExit]);
25 "-50%";
26 If([ProgramTypeCode] InList ("Transitional housing (HUD)" ;
27 "Services only program (HUD)") And
28 ([EarnedIncomeatExit] / [AdultLeaverCount] >= 0.4);
29 "Achieved";
30 If(([ProgramTypeCode] ="Transitional housing (HUD)" Or
31 [Program Type Code] ="Services only program (HUD)") And
32 [EarnedIncomeatExit] / [AdultLeaverCount] < 0.4;
33 FormatNumber ([EarnedIncomeatExit] /[AdultLeaverCount] - 0.4;
34 "#,##0.[%]%");
35 If(([ProgramTypeCode] ="Transitional housing (HUD)" Or
36 [ProgramTypeCode] ="Services only program (HUD)") And
37 [AdultLeaverCount] > 0 And
38 IsNull ([EarnedIncomeatExit]);
39 "-40%";
40 "N/A" ))))))))))