Show minimum and maximum per calendar week 1

[kernal]

I'm using crystal 9 with a txt file and a mysql calendar dates table.

Calendar dates mysql table

Date

01/01/2009
01/02/2009
every date until 12/31/2010

CSV file:

ID Submit_date class
1 Fri, 02/20/2009 10-5
2 Fri, 02/27/2009 120-1
3 Tue, 03/03/2009 111-6
4 Wed, 03/04/2009 120-7
5 Tue, 03/10/2009 333-8

I've inner joined with link type = calendar_date.date field to csv.submit_date.

My 1st group is the calendar_date.date field with the section will be printed for each week.
My 2nd group is id.
My 3rd group is class.

I have a formula to make unique records (unique record is ID with class)

I need to total unique records per week.

Problems:

1. Start the week with Monday and end on Sunday
2. Then count the unique records per week
3. Have on the report the minimum calendar date and maximum calendar date per week. Results would look like:

Mon Sun Total Unique Records

02/16 02/22 1
02/23 03/01 1
03/02 03/08 2
03/09 03/15 1

Help is appreciated. Thank you

 

[lbass]

Instead of using date on change of week, create a formula for your group #1:

//{@week}:
{table.date}-dayofweek({table.date},crMonday) + 1

Then you shyould be able to insert a distinctcount on your formula at the week level. For the minimum and maximum calendar per week, use:

totext({table.date}-dayofweek({table.date},crMonday)+1,"MM/dd") + " "+ totext({table.date}-dayofweek({table.date},crMonday)+7,"MM/dd")

Use this formula in the group header instead of the group name.

-LB

 

[kernal]

Works wonderfully. Thank you so much for helping me out so many times lbass.