Totals based on the same number of weeks for the current year's start date and 2 previous years 1

[borisbe]

I want the total # of persons every week for current year and 2 previous years based on start dates. I will be running this report weekly until December. We are trying to figure out if the numbers in the same weeks of a start date are higher or lower.

I'm using Crystal 9 with a csv file that has fields: Person ID, Start Date, Add Date (when person registered)

Example:

Start Dates
08/28/2013 is week #35
08/22/2012 is week #34
08/31/2011 is week #35

This week:

Start date: 08/28/2013 there are a total of 3 persons registered today and this is a week before the start date
Last Year's Start Date: 08/22/2012 How do I get the # of persons registered the week before this start date
2 Years ago Start Date: 08/31/2011 How do I get the # of persons registered the week before this start date

Mon, 08/26/2013- Sun, 09/01/2013 week:

Start date: 08/28/2013 say that there are a total of 5 persons registered when I run this report next week so it is the same week as the start date
Last Year's Start Date: 08/22/2012 How do I get the # of persons registered in the same week as this start date
2 Years ago Start Date: 08/31/2011 How do I get the # of persons registered in the same week as this start date

Mon, 09/02/2013-Sun, 09/08/2013 week:

Start date: 08/28/2013 say that there are now a total of 6 persons registered when I run this report in this week so 2nd week after the start date
Last Year's Start Date: 08/22/2012 How do I get the # of persons registered in the 2nd week after this start date
2 Years ago Start Date: 08/31/2011 How do I get the # of persons registered in the 2nd week after this start date

etc.

I hope I've given enough information and this makes sense. Help is VERY appreciated

 

[Madawc]

DatePart includes an option to get the week number.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[borisbe]

Thanks. I'll try the datepart.