Totals on a 12-month bucket

[victora]

Hello everybody:
I need to count the active patients per provider on a 12 month bucket (Jan - Dec). The patient table has 2 fields: StartEffDate(Start Effective Date) and EndEffDate (Termination Date).

Example: Start End
01/01/2003 03/15/2003--> add 1 to jan,feb,mar
01/01/2003 12/31/9999--> add 1 to jan - dec
05/01/2003 05/31/2003--> add 1 to may
06/15/2003 08/15/2003--> add 1 to jun,jul,aug
06/01/2002 12/31/9999--> add 1 to jan - dec

From the example above, a patient can be counted active even if its a partial membership for a month. Active patients has termination date of 12/31/9999 instead of null or zero.

Any help is highly appreciated.
Thank you.

 

[FVTrainer]

A basic approach is to create a formula for each month according to the pattern below (note: this is not the complete formula; it doesn't take into account the year aspect of your dates):

Jan Formula
-----------
If Month(EndEffDate) >= 1 then 1 else 0

Feb Formula
-----------
If Month(EndEffDate) >= 2 then 1 else 0

...etc.

The reason this formula is not sufficient by itself is that it doesn't take into account the year. But how you handle the year aspect of the dates may depend on the record filtering you're doing and whether or not you need to run this report for previous years or just the current year.

Anyway, after creating each formula, you would place them in the details section of the report and then create summaries at the provider group level to get the result you're looking for.

Are you running the report only for the current year? For any year?

 

[MJRBIM]

There might be a prettier way to do this, but this is my first idea...

Create one formula for each month and insert it in your detail based on these examples for the first 6 months of 2003.

Name : {@Jan_2003}
Formula : IF ((Year ({MonthCalc_txt.StartEffDate}) = 2003 AND Month ({MonthCalc_txt.StartEffDate}) = 1)
OR (Year ({MonthCalc_txt.EndEffDate}) = 2003 AND Month ({MonthCalc_txt.EndEffDate}) = 1)) THEN 1
ELSE
IF ((Date((Year({MonthCalc_txt.StartEffDate})),(Month ({MonthCalc_txt.StartEffDate})),1) <= Date (2003,1,1))
AND (Date((Year({MonthCalc_txt.EndEffDate})),(Month ({MonthCalc_txt.EndEffDate})),1) >= Date (2003,1,1))) THEN 1
ELSE 0

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Name : {@Feb_2003}
Formula : IF ((Year ({MonthCalc_txt.StartEffDate}) = 2003 AND Month ({MonthCalc_txt.StartEffDate}) = 2)
OR (Year ({MonthCalc_txt.EndEffDate}) = 2003 AND Month ({MonthCalc_txt.EndEffDate}) = 2)) THEN 1
ELSE
IF ((Date((Year({MonthCalc_txt.StartEffDate})),(Month ({MonthCalc_txt.StartEffDate})),1) <= Date (2003,2,1))
AND (Date((Year({MonthCalc_txt.EndEffDate})),(Month ({MonthCalc_txt.EndEffDate})),1) >= Date (2003,2,1))) THEN 1
ELSE 0

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Name : {@Mar_2003}
Formula : IF ((Year ({MonthCalc_txt.StartEffDate}) = 2003 AND Month ({MonthCalc_txt.StartEffDate}) = 3)
OR (Year ({MonthCalc_txt.EndEffDate}) = 2003 AND Month ({MonthCalc_txt.EndEffDate}) = 3)) THEN 1
ELSE
IF ((Date((Year({MonthCalc_txt.StartEffDate})),(Month ({MonthCalc_txt.StartEffDate})),1) <= Date (2003,3,1))
AND (Date((Year({MonthCalc_txt.EndEffDate})),(Month ({MonthCalc_txt.EndEffDate})),1) >= Date (2003,3,1))) THEN 1
ELSE 0

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Name : {@Apr_2003}
Formula : IF ((Year ({MonthCalc_txt.StartEffDate}) = 2003 AND Month ({MonthCalc_txt.StartEffDate}) = 4)
OR (Year ({MonthCalc_txt.EndEffDate}) = 2003 AND Month ({MonthCalc_txt.EndEffDate}) = 4)) THEN 1
ELSE
IF ((Date((Year({MonthCalc_txt.StartEffDate})),(Month ({MonthCalc_txt.StartEffDate})),1) <= Date (2003,4,1))
AND (Date((Year({MonthCalc_txt.EndEffDate})),(Month ({MonthCalc_txt.EndEffDate})),1) >= Date (2003,4,1))) THEN 1
ELSE 0

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Name : {@May_2003}
Formula : IF ((Year ({MonthCalc_txt.StartEffDate}) = 2003 AND Month ({MonthCalc_txt.StartEffDate}) = 5)
OR (Year ({MonthCalc_txt.EndEffDate}) = 2003 AND Month ({MonthCalc_txt.EndEffDate}) = 5)) THEN 1
ELSE
IF ((Date((Year({MonthCalc_txt.StartEffDate})),(Month ({MonthCalc_txt.StartEffDate})),1) <= Date (2003,5,1))
AND (Date((Year({MonthCalc_txt.EndEffDate})),(Month ({MonthCalc_txt.EndEffDate})),1) >= Date (2003,5,1))) THEN 1
ELSE 0

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Name : {@June_2003}
Formula : IF ((Year ({MonthCalc_txt.StartEffDate}) = 2003 AND Month ({MonthCalc_txt.StartEffDate}) = 6)
OR (Year ({MonthCalc_txt.EndEffDate}) = 2003 AND Month ({MonthCalc_txt.EndEffDate}) = 6)) THEN 1
ELSE
IF ((Date((Year({MonthCalc_txt.StartEffDate})),(Month ({MonthCalc_txt.StartEffDate})),1) <= Date (2003,6,1))
AND (Date((Year({MonthCalc_txt.EndEffDate})),(Month ({MonthCalc_txt.EndEffDate})),1) >= Date (2003,6,1))) THEN 1
ELSE 0


The data will return like this...

Patient StartEffDate EndEffDate Jan_2003 Feb_2003 Mar_2003 Apr_2003 May_2003 June_2003
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
A 01/01/2003 03/15/2003 1 1 1 0 0 0
B 01/01/2003 12/31/9999 1 1 1 1 1 1
C 05/01/2003 05/31/2003 0 0 0 0 1 0
D 06/15/2003 08/15/2003 0 0 0 0 0 1
E 06/01/2002 12/31/9999 1 1 1 1 1 1
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

 

[victora]

FVTrainer, yes, I'm thinking of letting the users enter any year.

MJRBIM, can a formula just accumulate my count? say, if ...then add 1 to Jan_bucket else
if ...then add 1 to Feb_bucket else ....

anyway, thanks guys.. i will take a shot.

 

[MJRBIM]

Yes, you could proably do it with declared Variables to add to your &quot;buckets&quot; within a single master formula using the same logic that you have within each month formula. My solution isn't as pretty, but it's functional and gives you the logic you need.

Also, you can replace &quot;2003&quot; in the monthly formulas I suggested with a numeric parameter value entered at run time to handle your user-input requirement.

FYI, in my formulas - you should replace &quot;MonthCalc_txt&quot; {MonthCalc_txt.StartEffDate} with your TableName.

 

[FVTrainer]

Let's suppose you have the users enter a year, in a parameter called {?Year}. Would record selection look like this:

{StartEffDate} <= {?Year} and (EndEffDate} >= {?Year}

Based on your question to MJRBIM, is your goal only to collect the counts and not to display the counts (or at least not all of them)? If so, then using variables to track the buckets (i.e., &quot;accumulate the counts&quot might seem more in keeping with your approach, but I don't see a real advantage to it. You would have fewer formulas, but the formulas you would have would be much more involved.