Aggregate values by week within each month
Posted on 2014-01-21
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 " &