COP18 Disagg Tool Change Log - achafetz/DataPack GitHub Wiki

Issues and Fixes to Both Disaggregation Tools

  • updated: March 8
  • current version in circulation: v2018.02.11

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

Initial Release - v2018.02.11

  1. Mixed up HTS_TST Neg <15 & POS/NEG 15+ Target formulas [Feb 16]
    • Issue: In the HTS Disagg Tool, the formulas for targets POS/NEG 15+ got placed in the opposite cells and target Neg <15 is missing an IFERROR in the formula
    • Affected Disagg Tool: HTS
    • Affected Tabs: ALL Modalities, expect Pediatics, Malnutrition, and KeyPop
    • Fix:
      1. Navigate to the IndexMod tab.
      2. In replace the formulas in DP Targets section for <15 Neg (AZ), 15+ Pos (BB), 15+ Neg (BC).
        • AZ7 =IFERROR(AX7 - AY7, 0)
        • BB7 =IFERROR(INDEX(targets[#Data],MATCH([@[psnu_type]]&" "&[@mechid],targets[psnu_type_mechid],0),MATCH(BB$6,targets[#Headers],0)),0)
        • BC7 = IFERROR(BA7 - BB7, 0)
      3. Copy this cells down to the rest of the columns in the row.
      4. Repeat this process for MobileMod - VCT and OtherPITC
      5. The process is slightly different for TBClinic, VMMC and PMTCT.
      6. For TBClinic, just add the IFERROR to the formula New Negative in the DP Targets section
        • AX7 = IFERROR(AV7 - AW7, 0)
      7. Copy this down to all the rows in the column.
      8. For VMMC and PMTCTANC, repeat the same process as with TBClinic, just in column AH, Newly Identified Negative.
        • AH7 = IFERROR(AF7 - AG7, 0)
      9. Copy this formula down to all the rows in the column.
      10. Save the workbook.
  2. Adding IFERROR to DP Target Calculations [Feb 19]
    • Issue - On a few of the Disagg Tool indicator tabs, a pseudo-numerator needs to be created to calculate disagg targets off of. When there is no Total Numerator or <15 data, the 15+/18+/<15 & 30+ pseudo-numerator can result in a formula error.
    • Affected Disagg Tool: Normal
    • Affected Tabs: OVC_SERV, TX_CURR, TX_NEW, TX_RET, VMMC_CIRC
    • Fix:
      1. Open up the Disagg Tool and navigate to the OVC_SERV tab.
      2. In the DP Target section, modify the following cells in each of the below indicator tabs to add in the IFERROR statement for OVC_SERV 18+. TX_CURR 15+, TX_NEW 15+, TX_RET_D 15+, TX_RET 15+, VMMC_CIRC <15 & 30+.
        • OVC_SERV - CA7 =IFERROR(BY7 - BZ7, 0)
        • TX_CURR - AF7 =IFERROR(AD7 - AE7, 0)
        • TX_NEW - AO7 =IFERROR(AM7 - AN7, 0)
        • TX_RET - AD7 =IFERROR(AB7 - AC7, 0)
        • TX_RET - AG =IFERROR(AE7 - AF7, 0)
        • VMMC_CIRC - AA7 =IFERROR(Y7 - Z7, 0)
      3. Save the workbook.
  3. Missing target lookup formula in the HTS_SELF tab [Feb 27]
    • Issue: The formula that looks up the target in the HTS_SELF tab is missing from the tool and needs to be added.
    • Affected Disagg Tool: Normal
    • Affected Tab: HTS_SELF
    • Fix:
      1. Open up the HTS_SELF tab of the Normal Disagg Tool and find the DP target section.
      2. In the first row of FY19 HTS_SELF, add the following formula:
        • BF7 =IFERROR(INDEX(targets[#Data],MATCH([@[psnu_type]]&" "&[@mechid],targets[psnu_type_mechid],0),MATCH(BF$6,targets[#Headers],0)),0)
      3. Copy the formula down to all the other rows in the column
      4. Save workbook
  4. HTS_SELF Disagg Target formatting [Feb 27]
    • Issue: All the disagg targets for HTS_SELF are formatted as percentages rather than numbers.
    • Affected Disagg Tool: Normal
    • Affected Tab: HTS_SELF
    • Fix:
      1. Navigate to HTS_SELF tab.
      2. Select the whole range of data in the Disagg Targets section, from the first row down to the last between columns BG:DA.
      3. To get to the cell formating, hit Ctrl + 1 (or Navigate through the ribbon at the top: Home > Number > More Number Formats)
      4. Select the Custom format on the left and then in the type, change the formatting to the following: #,##0;-#,##0;;
      5. Hit okay and save the workbook.
  5. Incorrect named ranges referenced [Feb 28]
    • Issue: The PMTCT_EID <2, KP_PREV and OVC_HIVSTAT variables names listed in the Already Alloc Targets different from the Allocation by SNUxIM tab and as a result, the targets aren't being looked up.
    • Affected Disagg Tool: Normal
    • Affected tab: Allocation by SNUxIM
    • Fix:
      1. Navigate to the Allocation by SNUxIM
      2. Replace the variables names for PMTCT_EID <2, KP_PREV and OVC_HIVSTAT with the variables names below.
        • EH6 = D_pmtct_eid_u2mo_fy19 [*add one space after fy19*]
        • GY6 = ovc_hivstat
        • HF6 = kp_prev_msm_sw
        • HH6 = kp_prev_msm_notsw
        • HJ6 = kp_prev_tg_sw
        • HL6 = kp_prev_tg_notsw
        • HN6 = kp_prev_fsw
        • HP6 = kp_prev_m_pwid
        • HR6 = kp_prev_f_pwid
        • HT6 = kp_prev_prison
      3. These changes should now match the Already Alloc Targets tab if variable names on Already Alloc Targets tab were not adjusted from their original version. Variable names in the Already Alloc Targets tab will need to match the ones below if they do not currently.
        • J6 = kp_prev_f_pwid
        • K6 = kp_prev_fsw
        • L6 = kp_prev_m_pwid
        • M6 = kp_prev_msm_notsw
        • N6 = kp_prev_msm_sw
        • O6 = kp_prev_prison
        • P6 = kp_prev_tg_notsw
        • Q6 = kp_prev_tg_sw
        • T6 = ovc_hivstat
        • W6 = D_pmtct_eid_u2mo_fy19 [*add one space after fy19*]
      4. Save workbook.
  6. KP_MAT Male and Female [Feb 28]
    • Issue: KP_MAT Total Numerator is determined in the Data Pack and the Disagg Tool assumes Male and Female are already allocated
    • Affected Disagg Tool: Normal
    • Affected tab: Already Alloc Targets
    • Fix:
      1. Copy the Already Alloc Targets tab, creating a new sheet.
      2. Rename the sheet KP_MAT
      3. Rename the column J, KP_MAT
      4. Change the text in cell J6 to D_kp_mat_fy19
      5. Rename column K, % Male and change the text in cell K6 to kp_mat_m_pct
      6. Change the cell format to %
      7. Delete columns L-Q
      8. In L7 and M8 add the following formulas and copy them down
        • L7 =IFERROR((1-[@[kp_mat_m_pct]])*[@[D_kp_mat_fy19]],0)
        • M8 =IFERROR([@[kp_mat_m_pct]]*[@[D_kp_mat_fy19]],0)
      9. Adjust the % in the % Male column to reflect the allocation of men to women.
      10. Go back to the original Already Alloc Targets tab and replace the formula in R & S and copy the formula down.
        • R7 = KP_MAT!L7
        • S7 = KP_MAT!M7
      11. Copy columns R and S. Copy and paste as values.
      12. Remove the KP_MAT column
      13. Save workbook.
  7. GEND_GBV ViolenceServiceType sums [Feb 28]
    • Issue: The formula at the end of the GEND_GBV tab are just summing the first row due to a missing character (@) in the formula. Additionally, Physical and/or Emotional Violence is summing up based on column headers that have "Violence" (which is both types) and should instead use "Physical"
    • Affected Disagg Tool: Normal
    • Affected tab: GEND_GBV
    • Fix:
      1. Navigate to the end of the GEND_GBV tab
      2. Replace the "Violence" with "Physical" in the formula and add an "@"
        • CH7 =SUMIF($AW$4:$CF$4,"*Physical*",gend_gbv_T[@[gend_gbv_u10_f_pev]:[gend_gbv_o50_m_sv]])
      3. Add an "@" to the formula in Sexual Violence (Post-Rape Care)
        • CI7 =SUMIF($AW$4:$CF$4,"*Care*",gend_gbv_T[@[gend_gbv_u10_f_pev]:[gend_gbv_o50_m_sv]])
      4. Copy down both rows to the other rows in the columns.
      5. Save the workbook.
  8. TX_RET age groups [Mar 1]
    • Issue: The formulas in columns AP and AQ refer to under 15 instead of over 15
    • Affected Disagg Tool: Normal
    • Affected tab: TX_RET
    • Fix:
      1. Navigate to column AP of the TX_RET tab
      2. Replace the "u15" with "o15" in two places in the formula
        • AP7 =tx_ret_T[@[D_tx_ret_o15_fy19]:[D_tx_ret_o15_fy19]]*[A_tx_ret_o15_f_pos]
      3. Repeat for column AQ
        • AQ7 =tx_ret_T[@[D_tx_ret_o15_fy19]:[D_tx_ret_o15_fy19]]*[A_tx_ret_o15_m_pos]
      4. Copy down that row to the other rows in the columns.
      5. Save the workbook.
  9. SUM formulas aggregating first table row rather than current row [March 6]
    • Issue: A few SUM formulas are missing the @ sign and aggregating first table row rather than current row.
    • Affected Disagg Tool: Normal
    • Affected Tab: HTS_SELF, PVLS
    • Fix:
      1. Open up the HTS_SELF tab of the Normal Disagg Tool and find the HIVSelfTest groupset in the Disagg target section.
      2. In row 7 of both targets, change the cell formulas to match the ones below:
        • CM7 =SUMIF($BG$4:$CL$5,"*Directly*",hts_self_T[@[hts_self_1014_f_direct]:[hts_self_o50_m_unasst]])
        • CN7 =SUMIF($BG$4:$CL$4,"*Unassisted*",hts_self_T[@[hts_self_1014_f_direct]:[hts_self_o50_m_unasst]])
      3. Copy the formula down to all the other rows in each column
      4. Switch over to the TX_PVLS tab and repeat this process for the targets in the DENOMINATOR - RoutineTargeted/HIVStatus section.
        • AN7 =SUMIF($AB$4:$AM$4,"*Routine",tx_pvls_T[@[tx_pvls_D_u15_f_rtn]:[tx_pvls_D_o15_m_undoctest]])
        • AO7 =SUMIF($AB$4:$AM$4,"*Targeted",tx_pvls_T[@[tx_pvls_D_u15_f_rtn]:[tx_pvls_D_o15_m_undoctest]])
        • AP7 =SUMIF($AB$4:$AM$4,"*Undocumented*",tx_pvls_T[@[tx_pvls_D_u15_f_rtn]:[tx_pvls_D_o15_m_undoctest]])
      5. Copy the formula down to all the other rows in each column
      6. Save workbook
  10. HTS_SELF Unassisted [March 6]
    • Issue: The HTS_SELF HIVSelfTestUser group set should have a denominator of Unassisted rather than the total numerator.
    • Affected Disagg Tool: Normal
    • Affected Tab: HTS_SELF
    • Fix:
      1. Open up the HTS_SELF tab of the Normal Disagg Tool and find the HIVSelfTestUser group set in the Disagg target section.
      2. In row 7 of both targets, change the cell formulas to match the ones below:
        • CO7 =hts_self_T[@[hts_self_unasst]:[hts_self_unasst]]*[A_hts_self_unasst_oth]
        • CP7 =hts_self_T[@[hts_self_unasst]:[hts_self_unasst]]*[A_hts_self_unasst_self]
        • CQ7 =hts_self_T[@[hts_self_unasst]:[hts_self_unasst]]*[A_hts_self_unasst_partner]
      3. Copy the formula down to all the other rows in each column
      4. Save workbook.
  11. Missing formula for TX_RET PregnantOrBreastfeeding [March 6]
    • Issue: TX_RET PregnantOrBreastfeeding disagg is missing a formula in the very last column of the table.
    • Affected Disagg Tool: Normal
    • Affected Tab: TX_RET
    • Fix:
      1. Open up the TX_RET tab of the Normal Disagg Tool and find the PregnantOrBreastfeeding group set in the Disagg target section.
      2. In row 7 of both targets, change the cell formula to match the one below:
        • AS7 =tx_ret_T[@[D_tx_ret_fy19]:[D_tx_ret_fy19]]*[A_tx_ret_preg_pos]
      3. Copy the formula down to all the other rows in the column
      4. Save workbook.
  12. Relative reference error with PP_PREV conditional formatting [March 8]
    • Issue: Percentages are remaining hidden on the PP_PREV tab as a result of missing a "$" in the conditional formatting formula, locking the reference to the target column.
    • Affected Disagg Tool: Normal
    • Affected Tab: PP_PREV
    • Fix:
      1. Navigate to the PP_PREV tab of the disagg tool and select any cell in the table.
      2. In the ribbon, find the Conditional formatting (Home > Styles > Conditional Formatting) and select Manage Rules
      3. From the buttons at the top of the pop-up, select Edit Rule.
      4. Replace the formula in the text box with the one below, adding a "$" before "AA"
        • =$AA7=0
      5. Hit OK, Apply, and OK again.
      6. Save workbook.