Count for each month in a 12 month period 1

[kime1974]

I am using Crystal XI and need to pull in counts for each month in a 12-month period. The 12-month period can be different each time it is run.

The data is entered with an effective and termination date to capture eligibility.

Here is an example of how someone can be in they eligibility system:

Joe Q. Employee
Effective 3/1/2009 no termination date

Jane Q. Employee
Effective 8/1/2008 no termination date

Adam J. Smith
Effective 12/1/2008 termination date of 3/31/2009

The starting date of the 12 month period for counts is 1/1/2009. Data should count as shown below:

Month 1 Counts (1/1/2009-1/31/2009) = 2
Month 2 Counts (2/1/2009-2/28/2009) = 2
Month 3 Counts (3/1/2009-3/31/2009) = 3 (Joe Q should now add to the count because he is effective 3/1/2009)
Month 4 Counts (4/1/2009-4/30/2009) = 2 (Adam J should now drop from the count becasue he termianted 3/31/2009)
Month 5 Counts (5/1/2009-5/31/2009) = 2
etc..

We had a previous report in Crystal 8.5 with a different eligibility system where we created a Parameter (Starting Date) and a formula for each month. It would look at the effective date, startingdate parameter and termination date to give a number to sum up later. Here is the syntax for Month 1 of the formula:

IIF (({EffectiveDate}<= {?StartingDate}) and ({Termdate}>= DateAdd ("d", -1, (DateAdd ("m",1 ,{?StartingDate} )))),1 , 0)

 

[lbass]

if {EffectiveDate} <= {?StartingDate} and
(
isnull({termdate}) or
{Termdate} >= dateserial(year({?StartingDate}),month({?StartingDate})+1,1)-1
) then 1

I would use dateserial, since dateadd for month just adds a 1 onto the month value, so the result doesn't necessarily correspond to the end of the next month. You also need to check for null termdates.

-LB

 

[kime1974]

Thanks lbass...I am still having a problem where it will not count someone if they have become effective after the starting date of the parameter.

For example:
Joe Q. Employee is effective 7/1/2009 with no termination date. We are wanting counts for each month starting with 1/1/2009. He does not add on for month 7. I am using just a date parameter for the end-user to key with no tie to any table, just a date.

if {EffectiveDate} <= {?StartingDate} and(isnull({TermDate}
or{TermDate} >= dateserial(year({?StartingDate}),month({?StartingDate})+1,7)-1) then 1

Did I put the 7 in the correct spot? It is pulling the record in the report, but not assigning a 1 to that formula field.

Thanks!
Kim

 

[lbass]

No, you should change the "+1" to "+7".

Actually, I think it should be:


//{@Month1}:
if {EffectiveDate} < dateserial(year({?StartingDate}),month({?StartingDate})+1,1) //effective before end of month 1
and
(
isnull({TermDate}) or
{TermDate} >= dateserial(year({?StartingDate}),month({?StartingDate}),1) //no term or term after start of month 1
) then 1

//{@Month2}:
if {EffectiveDate} < dateserial(year({?StartingDate}),month({?StartingDate})+2,1) //effective before end of month 2
and
(
isnull({TermDate}) or
{TermDate} >= dateserial(year({?StartingDate}),month({?StartingDate})+1,1) //no term or term after start of month 2
) then 1


In other words, the effective date is less than the end of the particular month (or from an earlier month) and there is no term or the term date is greater than or equal to the beginning of the selected month. This allows you to pick up cases where there is an effective date and term date within the same month, but also cases where neither date fall within the month.

-LB

 

[kime1974]

Wow! Thanks so much LB! This works slick! I was just about to give up and try to get someone to program this outside of Crystal!! Have a great weekend

 

[kime1974]

We have been using this report and found another scenario where it does not appropriately count. If the employee has a membership line effective 11/1/2003 and terminates on 7/31/2008 and then they add another membership line effective 8/1/2008 with no termination date (happens for various reasons), the person is being counted twice for 8/1/2008. Here is the formula for month 8:

if {EffectiveDate} < dateserial(year({?StartingDate}),month({?StartingDate})+8,1) and(isnull({TermDate}) or{TermDate}
>= dateserial(year({?StartingDate}),month({StartingDate}),1)) then 1

Thanks for your help!
~Kim

 

[lbass]

Your formula is incorrect. Should be:

if {EffectiveDate} < dateserial(year({?StartingDate}),month({?StartingDate})+8,1) and
(
isnull({TermDate}) or
{TermDate} >= dateserial(year({?StartingDate}),month({StartingDate})[red]+7[/red],1)
) then 1

-LB