Cross tab date grouping by week - changing week start date 2

[FrankTWall]

CRW X1, SQL DB

Issue: Have a cross tab with 3 columns
Column 1 - by Month
Column 2 - by week
Column 3 - by formula that forces "Week" (which is Mon - Friday) and then Sat and Sunday. i.e 3 groups within the week

Rows are by region

There is a date parameter and users will generally select a month or more data.

All working well except the users want the week - column 2 - to be Monday to Sunday (not Sunday to Saturday which is the CRW default) Therefore column 2 needs to be "grouped" by week, with Monday as the first day and as the display date.

Searching through here I cannot find any advice to force this week grouping change. I HAVE found where I can change the date display label for the week but , obviously, that only changes the display, not the group.

Any advice would be greatly appreciated.
Frank

 

[Madawc]

If you add one day to each date, it will then group as you want it to group. Do this in a formula field, something like

{your.date}+1

Group on the formula field, assuming it accepts it as a date. If not, you could convert it to suitable text values using ToText.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Use this formula {@weekno} as your row field:

datepart("ww",{table.date},crMonday)

While it is selected, click on group options->options->customize groupname->use a formula->x+2 and enter:

totext(dateadd("d",({@weekno}-1)*7, date(year({table.date}),1,1)-
dayofweek(date(year({table.date}),1,1),crMonday)+1),"MM/dd/yyyy")

-LB

 

[FrankTWall]

Many thanks to you both for your suggestions. Tried them both and had the greatest success with LBass's suggestion.

Great stuff, many thanks