Quarterly Data Analysis

Introduction

This document provides an analysis of the quarterly data for FY25 Q2. The analysis includes calculations for various metrics such as TX_CURR, TX_NEW, TX_PVLS_D, and others.

Setup

# Set working directory
setwd("C:/Users/oolatunbosun/Downloads/Mr Ayo's Request/txFY25_Q2_04052025")

# List files in the directory
list.files()

[1] "data.csv"               "txFY25_Q2_04052025.csv"

# Load necessary libraries
library(dplyr)

Warning: package 'dplyr' was built under R version 4.2.3

Attaching package: 'dplyr'

The following objects are masked from 'package:stats':

    filter, lag

The following objects are masked from 'package:base':

    intersect, setdiff, setequal, union

library(ggplot2)
library(lubridate)

Warning: package 'lubridate' was built under R version 4.2.3

Attaching package: 'lubridate'

The following objects are masked from 'package:base':

    date, intersect, setdiff, union

library(tidyr)

Warning: package 'tidyr' was built under R version 4.2.3

library(purrr)

Warning: package 'purrr' was built under R version 4.2.3

# Load the data
tll <- read.csv("txFY25_Q2_04052025.csv")

Data Transformation

Ensure relevant columns are in the correct format for further analysis.

tll <- tll %>%
  mutate(
    patient_stopped_treatment_date = as.Date(patient_stopped_treatment_date, format = "%Y-%m-%d"),
    patient_deceased_date = as.Date(patient_deceased_date, format = "%Y-%m-%d"),
    transferred_out_date = as.Date(transferred_out_date, format = "%Y-%m-%d"),
    transferred_in_date = as.Date(transferred_in_date, format = "%Y-%m-%d"),
    date_of_current_viral_load_Q2 = as.Date(date_of_current_viral_load_Q2, format = "%Y-%m-%d"),
    art_start_date = as.Date(art_start_date, format = "%Y-%m-%d"),
    current_viral_load_Q2 = as.numeric(current_viral_load_Q2),
    currentStatus_28_Q2 = as.character(currentStatus_28_Q2),
    ip = as.character(ip),
    state_name = as.character(state_name),
    datim_code = as.character(datim_code),
    sex = as.character(sex)
  )

Warning: There was 1 warning in `mutate()`.
ℹ In argument: `current_viral_load_Q2 = as.numeric(current_viral_load_Q2)`.
Caused by warning:
! NAs introduced by coercion

Define Reporting Periods

Define the relevant dates for the reporting period.

start_date <- as.Date("2025-01-01")
end_date <- as.Date("2025-03-31")
viral_load_start_date <- floor_date(end_date %m-% months(11), unit = "month")
six_months_prior <- ceiling_date(end_date %m-% months(6), "month") - days(1)
curr_period_start <- as.Date("2025-01-01")
curr_period_end <- as.Date("2025-03-31")
prev_period_start <- as.Date("2024-10-01")
prev_period_end <- as.Date("2024-12-31")

Metrics Calculation and Definitions

TX_NEW: Number of clients newly initiating ART in the reporting period

Definition: - Count of the total number of clients who started ART during the reporting period. - Exclude clients who are transferred in.

Algorithm:

tx_new <- tll %>%
  filter(
    art_start_date >= start_date & art_start_date <= end_date,
    !(patient_transferred_in == 1)
  ) %>%
  group_by(funder) %>%
  summarise(tx_new = n(), .groups = "drop")

---

TX_CURR: Number of clients currently on treatment at the end of the reporting period

Definition: - Count the total number of Active clients at the end of the current reporting period (Active = Last drug pick-up date + days of ARV refill + 28 days). The date must fall outside the reporting period. - This has been factored into column currentStatus_28_Q(X) of the current reporting period (X). - From clients with active status, exclude stopped & deceased in the current reporting period to validate active status.

Algorithm:

tx_curr <- tll %>%
  filter(
    currentStatus_28_Q2 == "Active",
    !(
      (!is.na(patient_stopped_treatment_date) & 
         patient_stopped_treatment_date >= start_date & 
         patient_stopped_treatment_date <= end_date) |
        (!is.na(patient_deceased_date) & 
           patient_deceased_date >= start_date & 
           patient_deceased_date <= end_date)
    )
  ) %>%
  group_by(funder) %>%
  summarise(tx_curr = n(), .groups = "drop")

---

TX_PVLS_D: Number of clients currently on treatment at the end of the reporting period who have documented VL result

Definition: - Count TX_CURR. - Exclude clients < 6 months on ART in the reporting period (not eligible for VL). - Count the number of clients who have a valid documented Viral Load result in the last 12 months from the end of the reporting period.

Algorithm:

tx_pvls_den <- tll %>%
  filter(
    currentStatus_28_Q2 == "Active",
    !(
      (!is.na(patient_stopped_treatment_date) & 
         patient_stopped_treatment_date >= start_date & 
         patient_stopped_treatment_date <= end_date) |
        (!is.na(patient_deceased_date) & 
           patient_deceased_date >= start_date & 
           patient_deceased_date <= end_date)
    ),
    art_start_date < as.Date("2024-10-01"),
    !is.na(date_of_current_viral_load_Q4) &
      date_of_current_viral_load_Q2 >= viral_load_start_date &
      date_of_current_viral_load_Q2 <= end_date
  ) %>%
  group_by(funder) %>%
  summarise(tx_pvls_den = n(), .groups = "drop")

---

TX_PVLS_N: Number of clients currently on treatment at the end of the reporting period who have documented VL results and achieved viral suppression

Definition: - Count TX_CURR. - Exclude clients < 6 months on ART in the reporting period (not eligible for VL). - Count the number of clients who have a valid documented suppressed Viral Load result (<1000 c/ml) in the last 12 months from the end of the reporting period.

Algorithm:

tx_pvls_num <- tll %>%
  filter(
    currentStatus_28_Q2 == "Active",
    !(
      (!is.na(patient_stopped_treatment_date) & 
         patient_stopped_treatment_date >= start_date & 
         patient_stopped_treatment_date <= end_date) |
        (!is.na(patient_deceased_date) & 
           patient_deceased_date >= start_date & 
           patient_deceased_date <= end_date)
    ),
    art_start_date < as.Date("2024-10-01"),
    !is.na(date_of_current_viral_load_Q4) &
      date_of_current_viral_load_Q2 >= viral_load_start_date &
      date_of_current_viral_load_Q2 <= end_date,
    !is.na(current_viral_load_Q2) &
      current_viral_load_Q2 < 1000
  ) %>%
  group_by(funder) %>%
  summarise(tx_pvls_num = n(), .groups = "drop")

---

Final Table Join

final_table <- tx_curr %>%
  full_join(tx_new, by = "funder") %>%
  full_join(tx_pvls_den, by = "funder") %>%
  full_join(tx_pvls_num, by = "funder") %>%
  mutate(
    tx_curr = replace_na(tx_curr, 0),
    tx_new = replace_na(tx_new, 0),
    tx_pvls_den = replace_na(tx_pvls_den, 0),
    tx_pvls_num = replace_na(tx_pvls_num, 0)
  )

Conclusion

This document has successfully calculated and joined the required metrics for the quarterly report. The final table is ready for reporting and visualization.