change start of week - graph

[sorcha999]

I am trying to create a report where the user enters a parameter for month and year. This report will then group averages by week and plot points on a graph for a full year. I need to group by week starting with the date they first enter and plot 52 points after that. So if the person enters 01/2003 (wednesday) the report will group that week wednesday-tuesday and continue until end of the year. If they enter 05/2003 the report should start grouping on Thursday and go until 04/2004 etc.

Any help would be greatly appreciated.

 

[lbass]

Here's one approach, although there might be a simpler one.
Create a string parameter {?MonthYr}. To ensure users enter 2 figures for the month, you could enter an edit mask of:

00/0000

Use a record select like:

{table.date} in date(val(right({?MonthYr},4)),
val(left({?MonthYr},2)),01) to
dateadd("ww",52, date(val(right({?MonthYr},4)),
val(left({?MonthYr},2)),01))-1

Then create a formula {@weeknumber}:

datevar monthyr := date(val(right({?MonthYr},4)),
val(left({?MonthYr},2)),01);

if datepart("ww",{table.date}, dayofweek(monthyr)) -
datepart("ww",monthyr,dayofweek(monthyr)) + 1 > 0 then
datepart("ww",{table.date}, dayofweek(monthyr)) -
datepart("ww",monthyr,dayofweek(monthyr)) + 1 else
52 + datepart("ww",{table.date}, dayofweek(monthyr)) -
datepart("ww",monthyr,dayofweek(monthyr)) + 1

Group on this formula. It will display the weeknumber starting with the parameter date (first day of the month/year) as week 1. If you want to, you could change the display of the weeknumber to the first day of that week by setting up a customized name when you create the group or by going to the change group expert->options->customize group name field->use a formula as a group name:

datevar monthyr := date(val(right({?MonthYr},4)),
val(left({?MonthYr},2)),01);
totext({table.date} - dayofweek({table.date},dayofweek(monthyr))+1,"MM/dd/yyyy&quot

-LB