Crystal 8.0 Group by Week by Year

[chudok]

I want my report to group by week(current week dates, Sun-Sat) and have seperate columns current year compared to last year Example:

2010 Date 2010 Revenue 2009 Revenue
17-Jan to 23-Jan 1,000 1,000
24-Jan to 30-Jan 3,000 2,000

When I ask for both years of data my week ranges take on 2009 instead of 2010. I have this grouping by week. How do I get this to group by this years weeks and show data form both 2010 and 2009. I would also prefer not to use date specific, rather something to the effect to year and year-1

 

[lbass]

But what date range would last year's data be for, since the same dates won't correspond to Sun-Sat, i.e., Jan 17, 2009 won't be a Sunday? Do you want to compare the same dates or the same week# per year? If the same week#, you need to specify what you count as week#1 each year.

-LB

 

[chudok]

Sorry about that, I want it to compare by week #. So week 1 2010 to week 1 2009. But I would still like it to list the week dates for the weeks of 2010. Does that make sense?

 

[chudok]

I don't know how to update my last post.....

My requirements for weeks would be sun-sat. The first week would be the first week ending date that starts in 2010 and 2009.

example:

week 1 2010 12/27/2009 - 1/2/2010
week 1 2009 12/28/2008 - 1/3/2009

 

[lbass]

If you use a number parameter for year, you can use a selection formula that returns the two years worth of data:

{table.date} in date({?Year}-1,1,1) to date({?Year},12,31)

Then insert a group on a formula {@week}:

datepart("ww",{table.date},crSunday, crFirstJan1)

Then create a formula to display the week range for the most recent year and place it in the group footer and remove the groupname from the group header:

totext(maximum({table.date},{@week})-dayofweek(maximum({table.date},{@week}))+1,"MM/dd/yyyy") + " to " +
totext(maximum({table.date},{@week})-dayofweek(maximum({table.date},{@week}))+7,"MM/dd/yyyy")

For your two columns, create a formula for each like this:

//{@MostRecentYr}:
if year({table.date}) = {?Year} then
{table.amount}

//{@PriorYr}:
if year({table.date}) = {?Year}-1 then
{table.amount}

Place these in the detail section and insert sums on them at the group level and then suppress the detail section and group header.

The only issue I'm not sure how you want to handle is that week#1 will always only contain the days (from multiple years) starting with January 1, not the entire week. Same with the last week of the year. You might want to look at the detail results to understand the grouping.

-LB