How to calculate a 30 day grace period?

[ericb1]

Thanks again to Lbass and Madawc for their help today. I'm trying a slight variation on my other report.

I have 2 dates, and I'm trying to calculate the following:

For the 1st day of each month, how many records have date1 before the previous month, and date2 after the first of the month, or are null?

So, for example, for 1/1/05, date1 is prior to 12/1/04 and date2 is greater than 1/1/05 or null.

I got Ken H.'s formula for finding the 1st of the month, but I just don't know how to write this out in Crystal format. Any help is greatly appreciated, thanks!

 

[lbass]

But where is the 1/1/05 coming from? Is it a parameter?

If it is a parameter, then you could use:

{table.date1} < dateserial(year({?date},month({?date})-1, 01) and
(
isnull({table.date2}) or
{table.date2} >= date(year({?date}),month({?date}),01)
)

Not sure whether you want <= or < and whether you want >= or >.

-LB

 

[ericb1]

I don't have the 1/1/05 date per se, I just want to use the regular calendar months. That's why I took Ken H's formula for figuring out the first of the month. I want to be able to say "For the 1st day of every month" from MM-DD-YY to MM-DD-YY as a range.

I can even prompt the user for the starting and ending dates for the report.

Ken's Formula for finding the 1st of the month:

WhileReadingRecords;
DateVar D:= currentdate; //Today's Date or any date field
Numbervar DOW:= 2; //The day of week you want with 1 being Sunday, 2 = Monday, etc
NumberVar Week:= 1; // The Week you want, 1 being the FIRST Monday of the month, 2 being the second, etc
DateVar BOM:= D - Day(D)+(8-DOW);
DateVar BOW:= BOM - DayOfWeek(BOM) + DOW + (7*(Week-1))

 

[Madawc]

You can get the calendar month using Month({your.date}). Does that help?

Madawc Williams (East Anglia, UK)

 

[lbass]

You use the phrase "30-day grace period"--it is unclear how that works with comparing two dates against an absolute date of the first day of the month. What exactly are your criteria for whether dates fall in a 30-day grace period?

-LB

 

[ericb1]

I think I've got the formula for the date, what I'm trying to do is loop through a date range, so I'm trying to say:

FOR EACH FIRST DAY OF THE MONTH FROM 1/1/2000 THROUGH 5/1/2005, CALCULATE THE FOLLOWING:

IF
{Claim_Releases.release_date} < dateserial(year({?Start Date}),month({?Start Date})-1, 01) AND
(
isnull({Claims.Release_Approval_Date}) or
{Claims.Release_Approval_Date} >= {?End Date}
)
THEN

"CLAIM OUTSTANDING"
ELSE
"CLAIM NOT OUTSTANDING

 

[ericb1]

Sorry, I finally understand what I'm trying to figure out (poor communication here)

I need to loop through a date range, not sure how to do this:

FOR EACH MONTH IN RANGE:

{table.date1} < [first day of month in range] and
(
isnull({table.date2}) or
{table.date2} > [first day of month in range]
)

END

The 30 day grace period is just that, trying to find where date1 and date2 are more than 30 days apart, based on the 1st day of each month.

Any help is greatly appreciated!

 

[lbass]

Use the date range to limit records in the selection formula as in:

(
isnull({Claims.Release_Approval_Date}) and
{Claim_Releases.release_date} in {?daterange}
) or
(
{Claim_Releases.release_date} in {?daterange} or
{Claims.Release_Approval_Date} in {?daterange}
)

Then create a formula:

if isnull({Claims.Release_Approval_Date}) then
(
if datediff("m",{Claim_Releases.release_date},currentdate-day(currentdate)+1) > 1 then
"Claim Outstanding" else "Claim Not Outstanding"
) else
if not isnull({Claims.Release_Approval_Date}) then
(
if datediff("m",{Claim_Releases.release_date},{Claims.Release_Approval_Date}-day({Claims.Release_Approval_Date})+1) > 1 then
"Claim Outstanding" else "Claim Not Outstanding"
)

-LB

 

[ericb1]

Thanks so much for your help lbass!

Just so I understand for myself (and hopefully can do this on my own next time

The line: datediff("m",{Claim_Releases.release_date},{Claims.Release_Approval_Date}-day({Claims.Release_Approval_Date})+1) > 1

means the diffence, in months, between release_date and release_approval_date, minus the day of the release_approval_date +1? I don't understand the 2nd part of the equation

I'm trying to base the difference on the literal 1st day of each month. So for 1/1/05:

if the release_date < 12/1/04 (1 month before 1/1/05) and the release_approval_date is > than 1/1/05, then "Outstanding" else "not outstanding". Then do the same thing for 2/1/05, 3/1/05, etc...

I actually think the formula in your 2nd post of this thread was correct, but I just don't know how to get the 1/1/05 into the report, it's not a paramenter in my data, just the regular calendar dates. That's why I was trying to use Ken H's formula to calculate the 1st day of each month, and then loop through it somehow, loop through the 1st day of each month for a range (say 2000 to 2005) and evaluate that formula for each first day of the month.

Sorry if I didn't explain myself well, you've been a big help. Ay help is appreciated, thanks again!

 

[lbass]

The second part of the formula just converts the Claims Release Approval Date to the first day of the month for the month it is in--since this is always the particular first of the month that you are looking for. then the formula checks to see if the Release Date is more than a month earlier than that. This should always evaluate correctly, without using a loop.

If you wanted to, you could group on:

if isnull({Claims.Release_Approval_Date}) then
currentdate-day(currentdate)+1 else
{Claims.Release_Approval_Date}-day({Claims.Release_Approval_Date})+1

This would give you the first of the months for those months where there were approval dates within the month.

And then do a second group on my last formula to get the Outstanding or Not Outstanding per month. However, if there were no approvals within a month, that month would not appear.

-LB

 

[ericb1]

Thanks again for your help! But it's not the first of the month based on either of these dates, that's the problem (not the problem with you, but the problem with the data and the way my boss wants to see the data. The 2 dates can span time irregardless of eachother, and that's why I think I need to loop through a date range and compare the 2 dates independently to the 1/1/05, 2/1/05, etc. When I just do a simple query on a sample month, with the dates hard coded, I get different counts.

Is there an easy way to loop through dates? Or some samples anywhere? I think I can just make a separate table of dates to make it easier?

Thanks again!

 

[lbass]

I thought that the dates absolutely related to each other--the grace period is dependent upon the difference between the two dates. Did you try my formulas?

-LB

 

[smcnulty2000]

I think, lbass, that ericb1 is trying to get crystal to generate the dates from 1/1/2000 all the way to 5/1/2005 without having a data source for those dates. It sounds like he's trying to use a for-next loop to generate the first of the month for each of these months and create a group out of this.

He wants to be sure that on each month there is a line of data whether there were records or not.

As I recall, you need a data source and a for-next loop won't do it. Making the table with the 65 dates will be required.

Just my interpretation, of course. But it would explain the confusion in the communication between you if true.

Scott.

 

[ericb1]

Yes, probably poor communication on my part. Now that I've got my formula (thanks to lbass), and I've created a separate table of just dates, how can I loop through them?

How do I write:

FOR EACH {date_range_dates.date}

FORMULA EVALUATION

END LOOP

Any help is greatly appreciated, thanks!

 

[smcnulty2000]

Sorry for leaving you hanging on this. Had stuff to do.

The thing I had in mind was using the new table with the dates to process the months, but rather than doing it inside of a crystal formula to bring in the new table as a data source and generate groups from it.

So you can do this at the data source level with a view or stored procedure, or you can make the new report with the current report as a subreport.

You build the new report, point it to your table to generate the groups by month, then you bring in a subreport that can process for previous and future records. You link the two using the linking wizard going date field of the main report to date field of the subreport.

Then you go inside the subreport and go to the record selection formula. You'll see that the formula has a {parameter} = {field} format.

(Where the parameter is the link from the main report and the field is the field from the subreport.)

You alter this formula so that it pulls in the data you want using the parameter. You are substituting the loop mechanism with a grouping mechanism inside of crystal.

Your new formula would resemble this kind of pseudocode.


{parameter} >= {field} -30 days (using dateadd)
and
{parameter} <= {field} + 30 days (also using dateadd)


Which would give you 30 days back from 2/1/2005 and 30 days forward from 2/1/2005. And since the main report is showing each and every month, the subreport will run for the first of the month for every month you have in your table.

Low performance, but it does do what you need it to do.

Scott.