Aggregate values by week within each month

Maybe I'm making this harder than it has to be.  I am simply trying to create a monthly report subtotaled by week.  I created a group and am using the Date field in an expression.  I've tried both ways below.  The first code is going to keep counting weeks successively instead of only within each month (I think).  The second code gives me (for the month to date of January), January Week 1, January Week 1, January Week 2 and January Week 3.  So it isn't grouping the weeks correctly.  Does anyone see anything obvious I am doing wrong, or does anyone have code that works for grouping by week within each month?

Code 1:  ="Week # " & DatePart(DateInterval.WeekOfYear, Fields!Date.Value)

Code 2:  =Format(Fields!Date.Value, "MMMM") & " Week " &
(Int(DateDiff("d", DateSerial(Year(Fields!Date.Value),
Month(Fields!Date.Value),1), Fields!Date.Value)/7)+1).ToString
LVL 1
Who is Participating?

I would definitely add this as an extra column in your sql query. It will be faster.
You could use:

Select ceiling(datepart(day, yourDateField) / 7) as Week

This would start with week 1 for every new month. Use the new column in your report for grouping.
0

I don't think there's a specific function that extracts the week numbers for a month only, so in that case you'd have to "calculate" the week numbers. And this is probably more easily done in your SQL script than in your report itself.
I don't have the time to write out an example, but you easily calculate the week number within a month for a given day by dividing the day by 7 and rounding up to the nearest integer. For example, day 12 would fall in the second week. 12 / 7 = 1.714285.... which rounds up to 2.
Put that in your SQL query and use that value in your report group.
0

BI ConsultantCommented:
As far as I understood you want to show a table with Month as top group and WeekNumber as subgroup, right?  Here's how I would do that:

First add a new calculated field to the dataset.  Call it WeekNumber and use your first expression:

=DatePart(DateInterval.WeekOfYear, Fields!Date.Value)

If Month is not a separate field I'd create a calculated field for that as well.  Depending on the requirements (is the report showing data for multiple years?) you may need to take year into account, for instance 201312 would be December 2013.

Secondly, set up table groupings using Month for the top group and WeekNumber for the subgroup.

Now you can use expressions like Sum(Fields!SomeNumber.Value) in the WeekNumber level cells to get your totals by week.

If the above is not what you're looking for, could you post a simple mockup of expected layout?
0

CIOAuthor Commented:
Kvwielink, I think that what you described is what I'm trying to do in my "code 2".  But my code is not working correctly.  Maybe it's because I'm missing a calculated field like you mention.  Where would I add that field, in the report or in the query?

Valentino, the problem I am having with solutions like that is that the week number will coontinue to increase throughout the year.  I want the week numbering to start over each month at week 1.  I will post a mockup like you suggest and maybe the query and report fields code too.

0

CIOAuthor Commented:
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.