COP18 Data Pack Change Log - achafetz/DataPack GitHub Wiki

Issues and Fixes to the Data Pack

  • updated: March 6
  • current version in circulation: v2018.02.05
    • for select OUs with hiearchy changes the current version is: v2018.02.13/15

Below is a running list of issues that affect the Data Pack for SI advisors to be aware of. To note any additional issues please alert SGAC_SI [at] state.gov

Initial Release - v2018.01.14

  1. Incorrect calculation for Peds LTFU [Jan 16]
    • Issue: formula references the wrong column in the LTFU calculation
    • Affected Tab: Target Calculation
    • Fix:
      1. Replace the first CS7 in the formula with CQ7 (in bold)
        • CW7 =IFERROR(MAX(0,((1-CQ7)*CV7)+((1-CS7)*CL7)),0)
      2. Copy the new formula in CW7 down to all rows in that column
      3. Save the workbook
  2. Highlighting not aligning to correct cells in the HTS tab [Jan 17]
    • Issue: conditional formatting references the wrong starting cell, throwing off the highlighting when a cell is changed
    • Affected Tab: HTS Target Calculation tab
    • Fix:
      1. Navigate to the HTS Target Calculation tab
      2. In the upper Home ribbon, select Conditional Formatting and choose Manage Rules at the bottom of the list
      3. On the top of the Conditional Formatting Rules Manager, change the top drop down from Current Selection to This Worksheet.
      4. Click on the first Rule with the gold highlighting and then Edit Rule.;
      5. In the formula, change the two instances of M5 to P5 as seen in the formula below
        • =P5<>dupHTSTargetCalc!P5
      6. Click OK in each of the two open windows and save the workbook
  3. Failure to remove EID from Adult HTS calculation [Jan 22]
    • Issue: EID should be excluded from the postives needed to identify. This is done for Peds but not for Adult HTS.
    • Affected Tab: HTS Target Calculation
    • Fix:
      1. Navigate to the HTS Target Calculation tab
      2. Change the formula in H7 to the one listed below, which removes EID from the total.
        • H7 =IFERROR(MAX(0,((INDEX(pos_ident,MATCH(snu_hts,snu,0))-INDEX(eid_treat,MATCH(snu_hts,snu,0)))/F7)-G7),0)
      3. Copy the new formula in H7 down to all rows in that column.
      4. Save the workbook
  4. SNU Priortization Reference [Jan 22]
    • Issue: The reference to SNU prioritization on the HTS calculation tab pulls in SNU1 rather than prioritization.
    • Affected Tab: HTS Target Calculation
    • Fix:
      1. Navigate to the HTS Target Calculation tab
      2. Change the formula in D7 to the one listed below.
        • D7 = INDEX(M_priority_snu,MATCH(snu,Msnulist,0))
      3. Copy the new formula in D7 down to all rows in that column.
      4. Save the workbook
  5. HTS/HTS_POS Key Indicator Trends Missing components [Jan 22]
    • Issue: The FY19 HTS and HTS_POS targets on the Key Indicator trends tab do not include testing and testing positive from the other entry points.
    • Affect Tab: Key Ind Trends
    • Fix:
      1. Navigate to the Key Ind Trends tab.
      2. Replace the formula in cell I7 with the one below, which includes the additional entry points.
        • I7 = IFERROR(INDEX(hts_need,MATCH(snu_trend,snu_hts,0))+INDEX(hts_tst_pmtctanc_fy19,MATCH(snu_trend,snu,0))+INDEX(hts_tst_tbclinic_fy19,MATCH(snu_trend,snu,0))+INDEX(hts_tst_vmmc_fy19,MATCH(snu_trend,snu,0)),"")
      3. Copy the new formula in I7 down to all rows in that column.
      4. Replace the formula in cell Q7 with the one below.
        • Q7 = IFERROR(INDEX(hts_pos,MATCH(snu_trend,snu_hts,0))+INDEX(hts_tst_pos_vmmc_fy19,MATCH(snu_trend,snu,0))+INDEX(tb_stat_newpos_fy19,MATCH(snu_trend,snu,0))+INDEX(pmtct_stat_newpos_fy19,MATCH(snu_trend,snu,0)),"")
      5. Copy the new formula in Q7 down to all rows in that column.
      6. Save the workbook
  6. HTS_TST Pediatric and Malnutrition distribution and yields missing [Jan 25]
    • Issue: The modalities Pediatric and Malnutrition for HTS_TST have a different age band and disaggregate than the rest of the testing modalities, so the distribution and yield data for these modalities was inadvertently left off.
    • Affected Tab: HTS Target Calculation
    • Fix:
      1. Download the DataPack_HTS_TST_u05_adj data file from PEPFAR Sharepoint
      2. Find the SNUs for your country & their associated Pediatric and Malnutrition data (Positive and Negative)
      3. Navigate to the DATIM Indicator tab and find the columns for
      4. Paste the data into the correct PSNU rows.
      5. Save the workbook.
  7. Incorrect SNU Target lookup formulas [Jan 25]
    • Issue: The SNU target tab has a number of columns that are using the wrong reference in the formulas
    • Affected Tab: SNU Targets
    • Fix:
      1. Navigate to the SNU Targets tab
      2. For each of the effected columns [FY19 Target TX_TB (Denom.), FY19 Target HTS_TST Key Populations, FY19 Target KP_PREV PWID Male (Denom.), FY19 Target KP_PREV PWID Male], copy the formula from below into the Data Pack cell matching the beginning of the formula line and copy the formula down to the rest of the column.
        • AH7 = INDEX(tx_tb_D_fy19,MATCH(Tsnulist,snu,0))
        • CG7 = INDEX(hts_tst_keypop_fy19,MATCH(Tsnulist,snu,0))
        • DE7 = INDEX(kp_prev_pwid_m_D_fy19,MATCH(Tsnulist,snu,0))
      3. Save the workbook.
  8. Missing allocations [Feb 5]
    • Issue: A few of variables are missing percent allocations - OVC_SERV, OVC_SERV Graduated, OVC_SERV Active, PP_PREV.
    • Affected Tab: SNUxIM Allocation
    • Fix:
      1. The only fix is to download a new Data Pack or calculate on own and add to the Data Pack
  9. VMMC Headings [Feb 5]
    • Issue: The COP year is incorrectly identified
    • Affected Tabs: Assumption Input, Target Calculation
    • Fix:
      1. Navigate to the Assumption Input tab
      2. Replace the COP year as COP17 in AX3 and AY3
      3. Move over to the Target Calculation tab and replace the COP year as COP17 in ES3 and ET3
  10. VMMC Coverage [Feb 5]
    • Issue: The calculation to identify expected coverage is pointed to the wrong numerator.
    • Affected Tab: Target Calculation
    • Fix:
      1. Navigate to the VMMC section of the Target Calculation tab
      2. Replace the formula in EZ to look up the expected number circumcised to expected 15-29 yo circumcised
        • EZ7 =IFERROR(EV7/EN7,0)
      3. Replace the formula in FA to look up the expected number circumcised to expected 15-29 yo circumcised
        • FA7 =IFERROR((EV7+EY7)/EN7,0)
      4. Save the workbook.
  11. OVC_SERV <18 Wrong Lookup [Feb 5]
    • Issue: OVC_SERV <18 should be a share of the total FY19 target, not FY17
    • Affected Tab: Target Calculation
    • Fix:
      1. Navigate to the OVC section of the Target Calculation tab
      2. Change the value in the FY19 OVC_SER <18 target to be a share of the FY19 Target
        • HC7 =IFERROR(HB7*GX7,"")
      3. Save workbook
  12. KP_PREV Prison Numerator Heading [Feb 5]
    • Issue: The KP_PREV Prisons and other closed settings numerator variable is labeled as the denominator
    • Affected Tab: Allocation by SNUxIM
    • Fix:
      1. Navigate to the Allocation by SNUxIM.
      2. Remove (Denom) from the heading in cells DK and HT3

Updated Version Release - v2018.02.05

All prior changes (8-12) have been resolved with this release. Any new issues and their fixes will be documented below.

  1. FY19 Target in Key Trends Formula Error [Feb 6]
    • Issue: In population, the formula to look up the relevant indicator FY19 target from the Target Calculation tab was incorrect and pasted in as text.
    • Affected Tab: Key Ind Trends
    • Fix:
      1. Open the Data Pack to the Key Ind Trends tab
      2. Replace the text in the indicated cell below and copy the formula down to all the other rows in the column.
        • X7 =IFERROR(INDEX(pmtct_art_fy19,MATCH(snu_trend,snu_hts,0)), "")
        • AE7 =IFERROR(INDEX(tb_art_fy19,MATCH(snu_trend,snu_hts,0)), "")
        • AL7 =IFERROR(INDEX(tx_curr_fy19,MATCH(snu_trend,snu_hts,0)), "")
        • AS7 =IFERROR(INDEX(tx_new_fy19,MATCH(snu_trend,snu_hts,0)), "")
        • AZ7 =IFERROR(INDEX(vmmc_circ_fy19,MATCH(snu_trend,snu_hts,0)), "")
  2. TB_STAT header labeling [Feb 8]
    • Issue: FY17 TB_STAT Known Pos is incorrectly labeled as PMTCT_STAT in the DATIM Indicator table
    • Affected tab: DATIM Indicator Table
    • Fix:
      1. Navigate to the DATIM Indicator table
      2. In the column titled "FY17 PMTCT_STAT Known positives at entry" (cell EO3) replace PMTCT_STAT with TB_STAT so that it will now read "FY17 TB_STAT Known positives at entry"
      3. Save the workbook
  3. Swapped Retention Rates [Feb 8]
    • Issue: The newly enrolled and 1+ yrs default assumption rates, overall and Peds are swapped.
    • Affected tab: Assumption Input
    • Fix:
      1. Open up the Assumption Input tab and change the Newly Enrolled Retention Rate to 90% and the Enrolled for 1+ yrs to 95% for ART and Pediatric ART
        • U5 = 90%
        • V5 = 95%
        • AK5 = 90%
        • AL7 = 95%
      2. Save the work book
  4. Prioritization in the Allocation by SNUxIM tab [Feb 9]
    • Issue: The formula in the Allocation tab looks up the historic snu prioritization rather than what may be potentially changed during the process in the Assumption tab (where the rest of the prioritizations are linked).
    • Affected tab: Assumption Input
    • Fix:
      1. Navigate to the Allocation by SNUxIM tab tab
      2. Replace the prioritization formula with the following
        • D7 =IFERROR(INDEX(M_priority_snu,MATCH(Dsnulist,Msnulist,0)),"NOT DEFINED")
      3. Copy the formula down to the rest of the column
      4. Save the workbook

Updated Version Release only for select OUs (tied to hierarchy adjustments) - v2018.02.13/v2018.02.15

  1. OVC_SERV allocation not tied to Mechanisms - only affects v2018.02.13/v2018.02.15 [Feb 13]
    • Issue: Due to an issue related to manually calculating the OVC_SERV total numerator, the allocations were just added to the PSNU level and not tied to any specific mechanisms. This does not affect countries using the v2018.02.05 version of the Data Pack.
    • Affected tab: Allocation by SNUxIM
    • Fix:
      1. Download the DataPack_OVC_SERV_alloc_adj data file from PEPFAR Sharepoint.
      2. Find the SNUxIMs for your country
      3. Navigate to the Allocation by SNUxIM tab and find the OVC_SERV allocation columns (column CL)
      4. Use the data from the adjustment to update/inform your OVC_SERV allocation
      5. Save the workbook.
  2. HTS_TST_POS <15 incorrectly reflecting 15+ [Feb 13]
    • Issue: In the DATIM Indicator Table tab, the HTS_TST Positives <15 actually is actually 15+
    • Affected tab: DATIM Indicator Table
    • Fix:
      1. Download the DataPack_HTS_TST_POS_u15_adj data file from PEPFAR Sharepoint.
      2. Find the SNUs for your country and copy the HTS_TST <15 data
      3. Navigate to the DATIM Indicator tab and find the FY17 HTS_TST_POS <15 column (column K)
      4. Paste the data into the correct PSNU rows.
      5. Save the workbook.
  3. Doubling up on OVC - only affects v2018.02.13/v2018.02.15[Feb 15]
    • Issue: The latest version of the ETL/Fact View dataset included OVC_SERV Total Numerator. Since this was manually done in the Data Pack scripts, it doubled the amount of OVC_SERV. This does not affect countries using the v2018.02.05 version of the Data Pack.
    • Affected tab: DATIM Indicator Table
    • Fix:
      1. Download the DataPack_OVC_SERV_adj data file from PEPFAR Sharepoint.
      2. Find the SNUs for your country and copy the OVC_SERV and OVC_SERV <18 data
      3. Navigate to the DATIM Indicator tab and find the FY17 OVC_SERV and FY17 OVC_SERV <18 columns (column CG + CI)
      4. Paste the data into the correct PSNU rows.
      5. Save the workbook.
  4. Missing PMTCT_ART Formula [Feb 15]
    • Issue: PMTCT_ART New IM targets are not showing up on the Allocation by SNUxIM tab as a result of a missing formula on the SNU Targets tab.
    • Affected tab: SNU targets
    • Fix:
      1. Navigate to the SNU Targets tab and find the FY19 Target PMTCT_ART Number of pregnant women already on ART before pregnancy column (column U)
      2. Add the formula below to U7:
        • U7 =INDEX(pmtct_art_new_fy19,MATCH(Tsnulist,snu,0))
      3. Copy the formula down to the rest of the rows in that column.
      4. Save the workbook.
  5. Prioritization Lookup [Feb 16]
    • Issue: The formula that looks up prioritizations in the Allocation by SNUxIM and Key Ind Trends tabs points to the prioritization in the DATIM indicator tab rather than the Assumption Input tab.
    • Affected tabs: Allocation by SNUxIM and KeyInd Trends
    • Fix:
      1. Navigate to the Allocation by SNUxIM tab.
      2. Change the formula in D7 to the one listed below.
        • D7 =IFERROR(INDEX(M_priority_snu,MATCH(Dsnulist,Msnulist,0)),"NOT DEFINED")
      3. Copy the new formula in D7 down to all rows in that column.
      4. Flip over to the Key Ind Trends tab.
      5. Change the formula in D7 to the one listed below.
        • D7 =IFERROR(INDEX(M_priority_snu,MATCH(snu_trend,Msnulist,0)),"NOT DEFINED")
      6. Copy the new formula in D7 down to all rows in that column.
      7. Save the workbook
  6. Change Missing from Blank to Zeros [Feb 20]
    • Issue: The formula in the Allocation by SNUxIM tab, the IFERROR formula replaces errors with blanks rather than zeros. This replacement has the potential to cause an issue when working with this data in the Disagg Tool.
    • Affected tab: Allocation by SNUxIM
    • Fix:
      1. Navigate to the Allocation by SNUxIM tab in the Data pack
      2. Highlight the row 7 of cells in the FY19 Target Allocation section (DP7:HX7)
      3. Hit Ctrl + H to do a find and replace (alternatively you can find it through the following navigation: Home > Editing > Find & Select > Replace)
      4. In the "Find what:" entry box enter: ""
      5. In the "Replace with:" entry box enter: 0
      6. Click the "Replace All" button and you should get a message that 113 replacements were made. Hit okay and close.
      7. Highlight the row 7 of cells in the FY19 Target Allocation section (DP7:HX7)
      8. Paste the rows down to all the other rows in the tab.
      9. Save the workbook.
  7. OPTIONAL: Allocation by SNUxIM target Check [Feb 21]
    • Issue: Given the number of targets that have to be allocated down to the IM level, it is very easy to under- or miss-allocate a target to mechanisms in a PSNU. This misalignment need to be resolved, otherwise your IM targets (in the Disagg Tool, and then Site Review Tool and finally DATIM) will not match the Data Pack. This "fix" provides a quick check at the top of the Allocation by SNUxIM tab for immediate feedback on whether IM targets match PSNU targets. Note that the inclusion of Mil targets in only the Target Calculation tab will throw off the check.
    • Affected tab: Allocation by SNUxIM
    • Fix:
      1. Open the Data Pack to the Allocation by SNUxIM tab's FY19 Target Allocation section.
      2. Add the below formula to the 2nd row of the tab. The order of the targets match between the SNU Target tab, which simply complies your targets from the Target Calculation tab, and the Allocation by SNUxIM tab, so the formula is extremly straight forward.
        • DP2 ='SNU Targets'!F5
      3. To see the value, you need to change the cell formatting. Hit Ctrl + 1 to pull up the Format Cell options (alternatively, you can get there through the follwoing navigation: Home > Number > More Number Options)
      4. In the Type box, add the following format and then close the pop up box: #,##0;;
      5. Copy the cell across the entire row (DP2:HX2)
      6. You can stop here or continue following to add conditional formatting to highlight cells that differ
      7. Highlight the entire row 2 for the FY19 Target Allocation section (DP2:HX2)
      8. Navigate to conditional formatting in the ribbon: Home > Styles > Conditional Formatting > New Rule
      9. Under the Select a Rule Type, pick the last option, Use a formula to determine which cells to format.
      10. In the Format values where this formula is true box, add the formula below:
        • =OR(DP5<(0.95 * DP2), DP5>(1.05 * DP2))
      11. Click Format and then Fill, and select Orange or a bright color to highlight indicators that don't match to the PSNU level. Hit Okay and Okay.
      12. Save the workbook.
  8. FY19 OVC_SERV Other Service, DREAMS [Feb 26]
    • Issue: The OVC_SERV Disaggs are created by multiplying a the percentage from your Assumption Input tab by the FY19 OVC_SERV Active target. For the Other Services disagg, the calculation in the Target Calculation tab is multiplying by the wrong percentage (Econ) to get the target.
    • Affected tab: Target Calculation
    • Fix:
      1. Open the Data Pack to the Target Calculation tab and scroll over to the OVC section.
      2. In the last column for OVC_SERV, replace the formula for OVC_SERV Other Service to multiplying the Active disagg by Other Service percentage (rather than Econ).
        • HO7 = IFERROR($HA7 * HN7,"")
      3. Copy the formula down to the other rows in that column.
      4. Save workbook.
  9. Peds Net New was not including PEPFAR coverage [March 6]
    • Issue: The formula to calculate Peds NET_NEW was missing PEPFAR's coverage (mimicking the calculation for total NET_NEW)
    • Affected Tab: Target Calculation
    • Fix:
      1. Open the Target Calculation tab to the Pediatric ART section
      2. Change the calculation in the NET_NEW tab to include PEPFAR's coverage
        • CV7 =IFERROR(MAX(0,CU7*(K7*CT7-MAX(R7,CL7))),0)
      3. Copy the formula down to all the other rows in the column
      4. Save workbook