group date by week?

[ericb123]

CR11, SQL2000

I have a table of data with a date field and a user name, and claim numbers, etc. So I'm grouping first on username, then on date (weekly).

Then I have a summary which is a count of claim numbers per week in the weekly grouping.

Now I'm trying to figure out how to do a weekly summary of the data. I get the total average by doing:

(Count ({table.ClaimNumber}, {table.Username}) / distinctcount({@Date}, {table.Username}))

which gives me the count of claim numbers per user for each day they appeared in a specific date. This works fine, but when I put in into the weekly group heading, it stays the same.

Is there a way to get this to group on the weekly group header? I tried adding a "weekly" to the criteria, but it doesn't like it.

Any help is greatly appreciated, thanks!

 

[lbass]

Use the following to get an average per week:

Count ({table.ClaimNumber}, {@Date},"weekly") / distinctcount({@Date}, {@Date},"weekly")

-LB

 

[ericb123]

thank you!