MediumLebowski
New Member
Ok, this sounds nutty, but it’s a real problem.
I have a list of clients by visit. Each client has an Assess date for each visit. Each client record carries a status indicator that says they are active or discharged. An active client is typically seen each quarter, but not always. Sometimes they skip a quarter, other times they might be seen twice during the same quarter.
I want to count active clients by quarter based on their status at the time of the reporting quarter, even if they don’t have an Assess date for each quarter that they can be inferred to be active. See examples in the attached workbook. I’ve already thought about requiring data entry for the missing dates. Wondering if there is a workaround.
Two unremarkable pivot tables are shown. The first just shows the Min and Max dates for each client and the start and stop quarters. The second shows the count I want to report -- by client by fiscal year fiscal quarter. The counts in the Pivot table don’t sum correctly, so a manual calculation is done above in row 28 using the COUNT function. This still doesn't work correctly because several clients who are active don't have Assess dates for the quarter when I want to count them. See Row 27 for "Should Be" counts.
BTW, the fiscal year and fiscal quarter ‘helper columns’ in the data table are formatted for the corporate fiscal year July-June. The Time Slicer on Assess Date for the pivot table (lower right) uses calendar values for the pick scale even though the pivot table displays the calculated fiscal year fiscal quarter header values. It’s confusing but can’t be helped.
I have a list of clients by visit. Each client has an Assess date for each visit. Each client record carries a status indicator that says they are active or discharged. An active client is typically seen each quarter, but not always. Sometimes they skip a quarter, other times they might be seen twice during the same quarter.
I want to count active clients by quarter based on their status at the time of the reporting quarter, even if they don’t have an Assess date for each quarter that they can be inferred to be active. See examples in the attached workbook. I’ve already thought about requiring data entry for the missing dates. Wondering if there is a workaround.
Two unremarkable pivot tables are shown. The first just shows the Min and Max dates for each client and the start and stop quarters. The second shows the count I want to report -- by client by fiscal year fiscal quarter. The counts in the Pivot table don’t sum correctly, so a manual calculation is done above in row 28 using the COUNT function. This still doesn't work correctly because several clients who are active don't have Assess dates for the quarter when I want to count them. See Row 27 for "Should Be" counts.
BTW, the fiscal year and fiscal quarter ‘helper columns’ in the data table are formatted for the corporate fiscal year July-June. The Time Slicer on Assess Date for the pivot table (lower right) uses calendar values for the pick scale even though the pivot table displays the calculated fiscal year fiscal quarter header values. It’s confusing but can’t be helped.