The caseload nobody checked

The quarterly report shows one office closing 38.9% of its cases for lost contact against 20.2% in another. The supervisor’s first instinct is that the caseworkers there are not trying hard enough.

The register has a caseworker column and twelve months of opening and closing dates. Nobody has used them together.

The file

protection-case-management-2024.v1.csv — 1,108 cases across six areas and seventeen caseworkers, with the month each case opened, the month it closed where it did, the closure reason and the number of case plan reviews held. Synthetic.

The task

Part one. Compute the monthly caseload for each caseworker: the number of cases open in each month. A case open from March to August counts in six months, not one. Report the mean and the peak per area.

Part two. Put the caseload beside two quality measures — case plan reviews per case, and the share of closures recorded as lost contact — and say in three sentences what the three columns together support and what they do not.

Part three. One area does not fit the pattern. Identify it, state how it differs, and give one alternative explanation for its numbers that the register cannot rule out.

Part four. Write the reply to the supervisor. Four sentences: what the lost contact rate is measuring, what the caseload table adds, what the one non-fitting area means for the conclusion, and what you would change first.

What you will need to handle

Three things, and none is a data error.

• 472 cases (42.6%) were still open at the December cut-off. They contribute to caseload in every month they were open, and they contribute nothing to time to closure. Treating them the same way in both calculations is wrong in one of them.
• One caseworker identifier appears under two areas, because a worker who transferred was re-registered rather than moved. Caseload per worker is right; caseload per worker per area splits one real workload in half.
• Seven cases close in a month earlier than they opened. They will produce negative durations and a caseload expansion that runs backwards.

Three reference points

Mean caseload runs from about 15 to about 33 across the six areas, and guidance puts an active GBV caseload near 25. If every area comes out under 20, the expansion is counting cases once rather than once per month they were open.

The questions to answer in prose

Three sentences each.

1. Lost contact is recorded by the caseworker who lost the contact. Explain what that does to the reliability of the column, and why a caseload table is better evidence about an office than a closure reason table is.

2. One area records 36.2% of its closures as administrative against under 10% elsewhere. Say what that is probably hiding, how you would check, and why it is a coding conversation rather than a statistical adjustment.

3. Your recommendation is likely to be about establishment rather than about performance. Say what would have to be true for the opposite conclusion to be right, and what evidence would distinguish the two.

What to hand in

An R script producing the per-worker monthly caseload, the area summary with mean and peak, the three-column quality table, and the four-sentence reply as a comment at the foot of the file. Tables printed and written to outputs/.

How to know you are done

Your caseload table has as many rows as there are caseworker-months with at least one open case, and its total across all workers and months equals the total number of case-months in the register. Check that identity in code — if it does not hold, the expansion has dropped or duplicated cases and every figure downstream is wrong.