Max Date in a Range

[sopomeres]

In CR8.5:

How can I capture the greatest {table.PayPeriodEndDate} that falls in the range of {table.HireDate} - 14 days.

Thank you.
Los

 

[Madawc]

Do a running total for {table.PayPeriodEndDate}, selecting the 'maximum' option. Include {table.HireDate} - 14 days as a rule for the running total

Madawc Williams (East Anglia)

 

[bdreed35]

Another option is to create a simple formula to determine dates that fall in the range:

@QualifyingPayPeriods

//find the pay period that was within 14 days prior to a hire date
if {table.PayPeriodEndDate} in ({table.HireDate} - 14) to {table.HireDate} then
{table.PayPeriodEndDate}
else
date(1900,1,1) //out of range default

Add this formula to your details section
Next, right click it and choose Insert, Summary.
In the dialog box, summary type should be maximum and location should be the grouping you want the summary for or Grand Total.

The advantages to this method are that you can move the summary to the Group Header or Report Header and it will display correctly. A running total will only be correct at the end of a group or report.
Also, if you need to see the maximum date in a grouping and for the whole report, you would need to create 2 seperate running totals to do this. With a summary field, you can just copy the summary field from one section and past it in another valid section (can't be details or page header/footer) and it will calculate correctly.

~Brian