• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count records that don’t exist by dates but are inferred by status

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.
 

Attachments

  • Calendar Overlay.xlsx
    22.9 KB · Views: 8
@MediumLebowski I took a shot at this, but was getting some different counts than yours. To get it to work without being a pain, I needed to reformat your Corp Fiscal Qtr column to show the years first. Cells D28:E30 have the min/max Assess Dates converted to quarter format.

Here's an issue. Steven's Min Assess Date was 9/16/2015, which translates into 15-Q1. If I'm understanding your requirements correctly, the client doesn't have to be seen in a given quarter to count. As long as they haven't been discharged, and they're active, they count. To me, the counts you're listing in I5:I16 are off in 15-Q1 through 15-Q4.
 

Attachments

  • Calendar Overlay.xlsx
    23.9 KB · Views: 4
@MediumLebowski I took a shot at this, but was getting some different counts than yours. To get it to work without being a pain, I needed to reformat your Corp Fiscal Qtr column to show the years first. Cells D28:E30 have the min/max Assess Dates converted to quarter format.

Here's an issue. Steven's Min Assess Date was 9/16/2015, which translates into 15-Q1. If I'm understanding your requirements correctly, the client doesn't have to be seen in a given quarter to count. As long as they haven't been discharged, and they're active, they count. To me, the counts you're listing in I5:I16 are off in 15-Q1 through 15-Q4.
 
Sorry this is so confusing. Given my druthers we would go to a calendar fiscal year in a New York minute! Steven's Min Assess Date is actually 16-Q1 because the fiscal year runs from July to June. September 2015 occurs during the 2016 fiscal year which ends in June 2016.
 
Thank you! To borrow the catch phrase of Tennessee Tuxedo--an animated character from the early '60s who got himself into many adventures--"Phineas J. Whoopee, you’re the greatest!"

But for the benefit of penguin brains like mine, could you explain what this formula is doing? :) I see it is an array formula that is summing the operation, but don't get what the operation is doing.
 
@MediumLebowski It's identifying every person in the A28:E30 table that have start dates before and end dates after the year-quarter in h5:h16. If a person started in or before that quarter, and were discharged after or during that quarter, they're counted. Getting this function to work was the main reason I needed the Year-Quarter format versus the Quarter-Year original.

Just FYI, a Sumproduct statement could replace the array formula. Array formulas do reach a point where they significantly slow down a spreadsheet.
 
Back
Top