• 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.

Counting valid records within periods

J

jonshurlock

New Member
Hi

I have a flat file of client IDs, Start Dates, End Dates, and 5 fields that will need to be filterable

I need to report on days, weekly periods, 4-weekly periods and monthly - this will also need to be filterable

I need to calculate how many of the clients are valid within each time period and then filter on different fields

Eg. Filter on weekly period, Age group 65+, area =south etc.

then return a chart with
Week 1 - number of valid clients
Week 2 - number of valid clients
... etc.


Or filter on calendar month periods, age group 18-64, area = north east

then return a chart with
Month 1 - number of valid clients
Month 2 - number of valid clients
... etc.

Does anyone have a tried and tested method? In SQL it would be relatively easy to create a table of date periods then join that with the client data, but that's not an option here
 
A pivot table would be great as I could add slicers to filter the data

Sample data
Client ID Start date End date Area
1 2/jan/2018 4/feb/2018 South
2 1/feb/2018 5/apr/2018 West

And periods by week, so 1jan-7jan 2018, 8jan-14jan 2018 etc.
 
jonshurlock
once again
... Have You tried with Pivot-table?
If You have data only as above,
then there would be a challenge
(= You would have much more valid data ... and with file)!
 
How would a pivot give me the required result? The problem is how to go from data at client level that has a start and end date, to output that has the total number of clients valid within time periods. AND be able to filter the output data on fields contained in the client level data

I would need output in the following format

Date Clients
South West
Week1 1 0
Week2 1 0
Week3 1 0
Week4 1 0
Week5 1 1
Week6 0 1


Etc.
 
jonshurlock
That data seems to be different than You've written before...
and
There should be at least wished layout with sample results too.

Questions:
Client ID 56: has been client those 25 days or how many?
Client ID 57: has been client form 02/08/2017 ... and still continues?
 
This is the data
Client 56: Client open for 26 days, now finished
Client 57: Client still open

The output is a line chart that shows for each period type (days or weeks or 4-weeks or months) how many clients are open within each period

So if the user chooses weeks as the period type, the needs to show how many clients are open between 1jan and 7jan, how many clients are open between 8jan and 14jan etc.

In addition, the chart needs to be filterable by Area, ServiceType, Profile, Long Term Flag Yn, Provider
 
Peter Bartholomew said:
If you use Debra's blog post
http://www.contextures.com/xlPivot07.html
to guide you on the grouping of dates (I believe Excel 2016 offers more functionality in this area) and use count of client IDs in the value field I would have thought you would have pretty much what you specified.

Otherwise you are going to be playing with COUNTIFS …
Click to expand...

I'm not sure this will work - I don't want to group start dates or end dates. It's counting the number of open clients within defined periods that is the problem
 
jonshurlock
Here is sample with You can get out daily/weekly/monthly client values...
I didn't test all ... of course!
 

Attachments

  • ExampleData.xlsb
    212.1 KB · Views: 8
Hi vletm

Thanks for this. Tricky one this, but it's not right. There are only 59 clients in the data so at the very least there shouldn't be any more than 59 for any one period (and probably a lot fewer). Not sure how the hidden column data has been calculated but it's not pulling in the right numbers. Sorry!

Calculating by hand for weekly periods I get this

01-Jan-17/ 07-Jan-17- 18
08-Jan-17/ 14-Jan-17- 19
15-Jan-17/ 21-Jan-17- 20
22-Jan-17/ 28-Jan-17- 20
01-Jan-18/ 07-Jan-18- 33
08-Jan-18/ 14-Jan-18- 35
15-Jan-18/ 21-Jan-18- 34
22-Jan-18/ 28-Jan-18- 34
29-Jan-18/ 04-Feb-18- 35
 
jonshurlock
Did You read my reply?
#1 line ... sample ... means also not ready!
#2 line ... not didn't tested
Those values from Pivot-table shows number of client days are in filtered period.
Did You checked, what do it give be weekly values?
 
@jonshurlock

I am still not sure what constitutes an 'open client'. Is it simply that the start date lies within the group of dates? If you require any record whose duration, start to end, overlaps each one of the date groups, more calculation is needed.
I am surprised that an SQL join would provide that, though.

I assume it is the second assumption that @vletm is addressing by expanding the dataset using VBA.
 
jonshurlock
Just a minute ...
Did You really get 'only' those nine rows by calculating by hand for weekly periods?
I could copy from You previous relpy's the 1st line .. but no?
There are many more weeks! ... Sorry!
I modified my sample.
Now, You could select from dropdown above Pivot-table period and
You'll get values.
... because, You didn't answer 'week' question, I used it as here!
 

Attachments

  • ExampleData.xlsb
    124 KB · Views: 7
The first day would be 10th Sep 2014 for filtering by days, Monday 8th Sep for filtering by week and 1st Sep 2014 for filtering by months

To answer the question about 'open client' it's easier to look at a couple of clients (although coding would involve the start or end date being within the period, or the start date being before the period and the end date being after the period)

Client 1. They have a start date of 15Mar2016 and no end date. Filtering by week period, this would mean they were closed in the week previous to opening, 7-13 March2016, and open in week 14-20Mar2016.

Client 5 with end date 25Mar2018: open in week 19-25mar2018, closed in week 26mar-1apr 2018

Using SQL would be relatively straight forward: use a recursive script to create a table of date period start and end dates (day/week/month with period flags), then join the client records to the period table where the client is open within each period

In the example dataset provided there are only 59 clients so there can never be more than that open at any one time, whichever period is chosen - day/week/month
 
vletm said:
jonshurlock
Just a minute ...
Did You really get 'only' those nine rows by calculating by hand for weekly periods?
I could copy from You previous relpy's the 1st line .. but no?
There are many more weeks! ... Sorry!
I modified my sample.
Now, You could select from dropdown above Pivot-table period and
You'll get values.
... because, You didn't answer 'week' question, I used it as here!
Click to expand...
Agreed, there are more than 9 rows, but since I calculated them by hand I wasn't going to calculate all 4 years' worth of data
 
Hi vletm, I've checked a few month period numbers by hand and your output looks spot on! I need to get my head around your vba, it looks comprehensive. These period types may change eg. they may also want quarters / calendar years / financial years but this will get amended as development goes ahead. So I was interested in a general method. Looks like you nailed it. Thank you
 
You must log in or register to reply here.
Back
Top