[b]Grouping By Week[/b]

[esings2him]

I am working on a report that shows the open order cash commitment for po's and I would like to group them by week and by month. My "by month" formula works when the user chooses that from the parameter; however, my "by week" parameter is a hit and miss. I would the like data below to display the Monday of the week as a header and the week should be Monday-Sunday. Currently, I have two group headers-the first one is on the table date to get the years to sort correctly, then I have the formula of the by week and by month. I created a summary field to display the minimum date next to the "Week of" thinking that would give me the first day of the week, but, clearly, I was wrong since the date, usually, isn't a Monday. How can I modify the formula to display the information that I'm wanting?!! Any help would be appreciated!


//@Date Period formula
IF {?Date Period} = "By Week" THEN
DATEPART("ww", {PORel.DueDate}, crMonday)
ELSE IF {?Date Period} = "By Month" THEN
DATEPART("m", {PORel.DueDate})


Week Of 05/01/2010Should be Week of 4/26/2010
5/1/2010 2890 1 1 24.00 0.00 24.00 $386,208.80

Week Of 05/17/2010 This is correct
5/17/2010 2528 1 1 1.00 0.00 1.00 $387,873.80
5/21/2010 2899 3 2 12.00 0.00 12.00 $411,873.80

Week Of 06/30/2010 Should be Week of 6/28/2010
6/30/2010 3433 1 1 36.00 5.00 31.00 $441,323.80

Week Of 08/19/2010 Should be Week of 8/16/2010
8/19/2010 3632 1 1 1.00 5.00 -4.00 $436,523.80
8/20/2010 3321 1 1 44.00 5.00 39.00 $436,796.80
8/20/2010 3617 1 1 2.00 5.00 -3.00 $435,928.54

 

[lbass]

//@Date Period formula
IF {?Date Period} = "By Week" THEN
DATEPART("ww", {PORel.DueDate}, crMonday, crFirstJan1) ELSE
IF {?Date Period} = "By Month" THEN
DATEPART("m", {PORel.DueDate})

Group on the above formula and then customize the groupname using a formula:

numbervar x := DATEPART("ww", {PORel.DueDate}, crMonday, crFirstJan1);
IF {?Date Period} = "By Week" THEN
totext(DATEADD("ww",x-1, date(year({POrel.DueDate}),1,1)-
dayofweek(date(year({POrel.DueDate}),1,1),crMonday)+1),"MM/dd/yyyy") ELSE
IF {?Date Period} = "By Month" THEN
monthname(DATEPART("m", {PORel.DueDate}))

-LB

 

[esings2him]

Lbass,
I forgot to mention we have some fields that don't have dates so I'm getting "Dates must be between year 1 and year 9999. How to have it say if the date equals null or 0 or 1/0/1900 then "no date available" else display the formula?

 

[lbass]

//@Date Period formula
IF {?Date Period} = "By Week" THEN
(
if isnull({PORel.DueDate}) then
0 else
DATEPART("ww", {PORel.DueDate}, crMonday, crFirstJan1)
) ELSE
IF {?Date Period} = "By Month" THEN
(
if isnull({PORel.DueDate}) then
0 else
DATEPART("m", {PORel.DueDate})
)

Group on the above formula and then customize the groupname using a formula:

numbervar x;
if not isnull({PORel.DueDate}) then
x := DATEPART("ww", {PORel.DueDate}, crMonday, crFirstJan1);
IF {?Date Period} = "By Week" THEN
(
if isnull({PORel.DueDate}) then
"No Date Available" else
totext(DATEADD("ww",x-1, date(year({POrel.DueDate}),1,1)-
dayofweek(date(year({POrel.DueDate}),1,1),crMonday)+1),"MM/dd/yyyy")
) ELSE
IF {?Date Period} = "By Month" THEN
(
if isnull({PORel.DueDate}) then
"No Date Available" else
monthname(DATEPART("m", {PORel.DueDate}))
)

-LB

 

[esings2him]

Lbass,
Still getting the same error message-Dates must be between year 1 and year 9999 and its highlighting this part of the formula: date(year({POrel.DueDate}),1,1)

 

[lbass]

That appears in multiple places. I think you need to verify what kinds of values your date field contains.

After each null check you could add a line like this:

if isnull({PORel.DueDate}) or
{PORel.DueDate} = date(0,0,0) or
{PORel.DueDate} = date(1901,1,0) then...

Also check file->report options and make sure "convert nulls to default values" is not checked.

-LB

 

[esings2him]

Lbass,
Thank you! It is grouping on the weeks now displaying the Monday of the week; the convert nulls to default values was checked. However, it is still not sorting the years correctly. I'm getting
4/4/2011
4/26/2010
5/02/2011
5/17/2010
Is there a way to correct this? Let me know!! Thanks for all your help!! You are the BEST!!

 

[lbass]

Insert a group on years first (you can suppress it if you like) and then add your conditional formula as the second group. I didn't notice that you were using multiple years.

-LB

 

[esings2him]

Worked like a charm! Thanks Lbass!!