Need Help with Dates

[Myla944]

Hello,

I am using Crystal Ver 9.2.2.693 & my information that I am making reports for is a SQL Database.

I created several reports that show all issues on our many routes. This report shows current year issues (i.e. damaged product, ticket not signed, etc..) and this report also shows prior year, using a parameter selection of one date and the formulas below:

Current Year formula:
if year ({tblStoreReport.IncidentDateTime}) = year ({?Year})
and {tblCompany.CompanyName} = {?Bakery}
then switch ({tblStoreReport.Subcat} >0, 1)

Prior Year Formula:
if Year ({tblStoreReport.IncidentDateTime}) = Year ({?Year}) - 1
and {tblCompany.CompanyName} = {?Bakery}
then switch ({tblStoreReport.Subcat}>0,1)

This is an example how my current report looks:

# of Store Rpts # of Store Rpts YTD Variance
2006 2007 %

Delivery Issues
Missed Deliveries
Driver Issues
Tickets not signed

I was asked to create this same report but to make a parameter using a date range to show information from Monday to Sunday based on the users selection, which I created a new Parameter Field and also changed the select expert to this formula: {tblStoreReport.IncidentDateTime}<={?Date Range}.

I then created the following formula for the current year:

if {tblStoreReport.IncidentDateTime} = {?Date Range}
and {tblCompany.CompanyName} = {?Bakery}
then switch ({tblStoreReport.Subcat} >0, 1)

The formula above works great for the current year, but I need to use this same week range for the prior year and I am stumped on how to make it work. (example - current year 11/13/06 to 11/19/06 (Mon to Sunday), prior year would be the week range of 11/14/06 to 11/20/06 (Mon to Sunday))
Can anyone please help?? I am not an expert on Crystal and I am confused on this one. Thank you!

Sincerely,

Myla944

 

[lbass]

Use the following for the prior year week range:

datevar x := minimum({?daterange});
if {tblStoreReport.IncidentDateTime} in
dateserial(year(x)-1,month(x),day(x))-
dayofweek(dateserial(year(x)-1,month(x),day(x)),crMonday)+1 to
dateserial(year(x)-1,month(x),day(x))-
dayofweek(dateserial(year(x)-1,month(x),day(x)),crMonday)+7 and
{tblCompany.CompanyName} = {?Bakery} then
switch ({tblStoreReport.Subcat} >0, 1)

This uses the minimum of the daterange to determine what is the corresponding week--not sure if this is what you want.

-LB

 

[Myla944]

Thank you LB,

Oh thank you! I shall try this out shortly & let you know if it succeeds (crossing fingers).

Sincerely,

Myla