Solved

Aggregate values by week within each month

Posted on 2014-01-21
5
781 Views
Last Modified: 2014-01-22
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
0
Comment
Question by:fabi2004
  • 2
  • 2
5 Comments
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39799096
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39799218
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
 
LVL 1

Author Comment

by:fabi2004
ID: 39800071
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.

Thanks so much for answering.
0
 
LVL 12

Accepted Solution

by:
Koen Van Wielink earned 500 total points
ID: 39800172
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
 
LVL 1

Author Closing Comment

by:fabi2004
ID: 39801258
Thank you!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now