Flag Logic Needed to Identify Problems for Flag Resolutions

[eccovia-csmith]

Several CoCs we support have raised questions about flags comparing Inventory and LSACalculated Utilization rates. Specifically, we are examining Flag IDs 984, 989, and 999, which indicate that there are active beds specified in Inventory, but the Average Utilization rate is reported as 0.

As a vendor, we need a better understanding of how HDX 2.0 calculates these utilization rates based on the LSA Export data. The 'Full Flag List for Vendors' provides this logic:
fx_avg_util_all_all_es=0 & expected_reportstart=reportstart & expected_reportend=reportend
with additional flag variables: fx_avg_ppl_all_all_es fx_avg_beds_tot_h_all_es fx_avg_util_all_all_es

However, terms like 'fx_avg_util_all_all_es' are not defined, making it difficult for us to interpret or apply the logic. This lack of decipherable information prevents us from understanding which data is used to compute these numbers. To effectively troubleshoot and explain these flags, we need insight into how HDX calculates them, as the existing logic appears as a 'black box' to us.

Logic used to generate Flag IDs 984, 989, and 999 are needed in the immediate term to help us investigate the flags one of our communities is experiencing to help them with an explanation or resolution prior to the final submission of their LSA.

Request for Improvement:
We recommend increasing transparency regarding flag calculations to improve the efficiency of troubleshooting within the LSA and related data issues flagged by HDX 2.0. Without clear logic, it's unreasonable to expect vendors or communities to resolve or explain flags adequately and accurately.

As a vendor, we consistently aim to assist communities in addressing unexpected or incorrect flags. Often, these stem from simple data quality issues disconnected from the flag itself due to LSA process complexities. Occasionally, we identify logic discrepancies in our LSA data processing. However, comprehending how HDX identified an issue remains crucial.

Reverse-engineering HDX's conclusions is currently unreliable and inefficient. Future LSAs should provide clearer explanations for flags with complex logic. Access to the underlying logic behind 'fx_' prefixed functions/calculations would enable our engineering team to better understand and support the communities we serve.

[LaurenBianchi]

I agree, this has been a challenge during troubleshooting and trying to decode the multiple LSA documents utilized - this would be tremendously helpful for next year's reporting period. Thank you for your time and consideration!

[cwanska]

I sent this reply to our email thread but wanted to paste it here as well so anyone could benefit. We will continue to talk about what level of detail would be helpful for vendors trying to get information about flag logic.

Identify Projects

1. Identify projects to be included based on the following criteria:
   a. “HMISParticipationType” = 1, with “HMISParticipationStatusStartDate” and “HMISParticipationStatusEndDate” overlapping at least partially with the reporting period (see HMISParticipation.csv file)
   b. “ProjectType” = 0 or 1 (see Project.csv file)
   c. “HousingType” is not equal to 3 (see Project.csv file)
   d. Exclude projects for which “Availability” is equal to 3 or null in all inventory records (in other words, exclude projects for which the maximum and minimum values of “Availability” are both zero; see Inventory.csv file)
   Calculate Bed Nights
2. For each project ID identified in step 1, find the number in the “Value” column of LSACalculated.csv where the “Cohort” column = 1, “Population” = 0, “ReportRow” = 56, and ProjectID = [each project ID identified in step 1]. Call this number nights_all.
3. For each value of nights_all, divide the number by 366. Call this new number fx_avg_ppl_all.
   Calculate Beds
4. For each project ID identified in step 1, find the following numbers:
   a. Find the number in the “Value” column of LSACalculated.csv where the “HHType” column = 1, “Cohort” = 1, “Population” = 0, “ReportRow” = 56, and ProjectID = [each project ID identified in step 1]. Call this number Value1.
   b. Find the number in the “Value” column of LSACalculated.csv where the “HHType” column = 2, “Cohort” = 1, “Population” = 0, “ReportRow” = 56, and ProjectID = [each project ID identified in step 1]. Call this number Value2.
   c. Find the number in the “Value” column of LSACalculated.csv where the “HHType” column = 3, “Cohort” = 1, “Population” = 0, “ReportRow” = 56, and ProjectID = [each project ID identified in step 1]. Call this number Value3.
   d. Find the sum of Value1, Value2, and Value3 (which are the numbers you calculated in steps 4a-c). Call this sum avg_beds_tot_temp.
5. Filter the Inventory.csv file:
   a. Filter the “ProjectID” column to include only the projects identified in step 1
   b. Filter the “ESBedType” column to exclude rows where “ESBedType” = 2
   c. Filter the “InventoryStartDate” and “InventoryEndDate” columns to include only rows where these dates overlap with the LSA reporting period.
   d. Filter the “HouseholdType” column to include only the household type to which the flag refers. For flag 989, filter to AO projects (HouseholdType = 1), and for flag 999, filter to CO projects (HouseholdType = 4)
6. For each row in the filtered Inventory.csv file, calculate the following numbers:
   a. Find the maximum of “InventoryStartDate” (which is one of the columns in Inventory.csv) and the LSA reporting period start date (which is 10/1/2023). In other words, if InventoryStartDate is a date before 10/1/2023, replace it with 10/1/2023. But if InventoryStartDate is a date after 10/1/2023, do not change it. Call this new date alt_InventoryStartDate.
   b. Find the minimum of “InventoryEndDate” (which is one of the columns in Inventory.csv) and the LSA reporting period end date (which is 9/30/2024). In other words, if InventoryEndDate is a date after 9/30/2024, replace it with 9/30/2024. But if InventoryEndDate is a date before 9/30/2024, do not change it. Call this new date alt_InventoryEndDate.
   c. Find the difference in days between alt_InventoryEndDate and alt_InventoryStartDate. Add 1 to this number. Multiply the result by the value in the “BedInventory” column, and divide by 366. Call this number avg_beds_tot.
   i. [Example: if “BedInventory” is 20, alt_InventoryEndDate is 11/30/2023, and alt_InventoryStartDate is 11/20/2023, you would find the difference in days (these dates are 10 days apart), add 1 (which gives you 11), multiply that by 20 (which gives you 220), and divide by 366 (220/366 = 0.60). So your value of avg_beds_tot is 0.60 for this row.]
   ii. If there are multiple rows for the same ProjectID in the Inventory.csv file, find the sum of avg_beds_tot for each ProjectID so that you have only one value of avg_beds_tot for each project.
   d. Repeat steps 6a-c, this time removing the filter on HouseholdType to include all inventory records regardless of household type. Call the resulting number avg_beds_tot0.
   Calculate Utilization
7. For each project ID identified in step 1, do the following:
   a. If avg_beds_tot_temp = 0, use the number avg_beds_tot. Otherwise, multiple avg_beds_tot0 by [value1 for flag 989 or value3 for flag 999] and divide the result by avg_beds_tot_temp. [Note that italicized names are defined in the steps above.] Call this number avg2_beds_tot.
8. For each project ID identified in step 1, divide fx_avg_ppl_all by avg2_beds_tot. Call the result fx_avg2_util_all.
9. You should now have a value of fx_avg2_util_all for every project ID identified in step 1. Take the sum of these numbers. If the sum is zero, the flag will trigger.

[cwanska]

I agree, this has been a challenge during troubleshooting and trying to decode the multiple LSA documents utilized - this would be tremendously helpful for next year's reporting period. Thank you for your time and consideration!

@LaurenBianchi I'm not sure if GitHub messaged you since you're not the creator of this issue so I wanted to highlight my reply here and we will continue to discuss. Thanks!

[cwanska]

@eccovia-csmith here is a similar write up for flag 984
Identify Projects

1. Identify projects to be included based on the following criteria:
   a. “HMISParticipationType” = 1, with “HMISParticipationStatusStartDate” and “HMISParticipationStatusEndDate” overlapping at least partially with the reporting period (see HMISParticipation.csv file)
   b. “ProjectType” = 0 or 1 (see Project.csv file)
   c. “HousingType” is not equal to 3 (see Project.csv file)
   d. The “InventoryStartDate” and “InventoryEndDate” columns of Inventory.csv overlap with the LSA reporting period for at least one Inventory.csv row for this Project ID.
   e. Exclude projects for which “ESBedType” is always equal to 2 in the Inventory.csv rows corresponding to this Project ID
   f. Exclude projects for which the “Availability” column of Inventory.csv is always equal to 3
   Calculate Bed Nights
2. For each project ID identified in step 1, find the number in the “Value” column of LSACalculated.csv where the “Cohort” column = 1, “HHType” = 0, “Population” = 0, “ReportRow” = 56, and ProjectID = [each project ID identified in step 1]. Call this number nights_all.
3. For each value of nights_all, divide the number by 366. Call this new number fx_avg_ppl_all.
   Determine whether flag will trigger
4. You should now have calculated a value of fx_avg_ppl_all for each Project ID identified in step 1. Calculate the sum of fx_avg_ppl_all adding across projects. If the sum is equal to 0, the flag will trigger.