cross tab help 1

[nathanharcup]

Hi All,
I hope you can help me out with an issue I have.

I have a cross tab report which currently looks like the below:-

weeknumber 45 46
team
support %ontarget %ontarget

network %ontarget %ontarget

I want to display the first monday for week 45 instead of 45 which is the week number

Any ideas

 

[lbass]

This depends upon what you count as the first week of the year and first day of the week (Sunday?). The first week of the year options are:

Week containing Jan 1
First week containing four or more days
First full week in the year

-LB

 

[nathanharcup]

hi thanks it should run for the first week containing january the 1st

Thanks

 

[lbass]

So Monday is the first day of the week for the purposes of counting the weeks or is Sunday?

-LB

 

[lbass]

In the crosstab, select the column field (weeks)->group options->option tab->customize groupname->use a formula and enter:

totext(dateadd("ww",{@week}-1,date(year({table.date}),1,1)-dayofweek(date(year({table.date}),1,1),crMonday)+1),"MM/dd/yyyy")

This assumes you limit the records to one calendar year in the record selection formula. {@week} is your weeknumber formula which should look like:

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

-LB

 

[nathanharcup]

Thanks a lot that was just what i was looking for