Summarize Records by Date Range?

[Emerald IT]

I'm trying to summarize hours worked for a customer for a week. I can group employees hours for a specific customer by date but how do I group by work week (monday-sunday)?

[keithwilhoit]

Re: Summarize Records by Date Range?

20080719GroupingByWeeks

In answer to your question, �I'm trying to summarize hours worked for a customer for a week. I can group employees hours for a specific customer by date but how do I group by work week (Monday - Sunday)?�,--

One answer--the easiest way is to use the function Week() in creating a calculated field, that is Weekgroup = Week(txndate). The field weekgroup can be used in the report. To make subtotals by weekgroup, it will be necessary to add a Group Break in the report and use week(txndate) in an expression for the break, as the calculated field will not be available as an option. That is, when inserting a break in the report, it will be necessary to select a field or an expression for the break to change on. It is necessary in this case to select the expression and enter the function Week(txndate).

The only problem with this method is that you asked for the week to be Mon-Sun. The function provides calendar weeks, Sun-Sat.

If you must have the weeks as Mon-Sun, one way to get there, though tedious and not elegant, is to build your own calculated field and define the weeks yourself. If you only need a few weeks, that is not too bad, but if you need all 52 weeks, that will be time consuming. The same expression used in the calculated field must be used in the Group/Break expression.

Another approach would be to create a new table in the QReportBuilder control panel, append the data from the QReportBuilder passive file and build your weekgroup field using field rules�which is essentially the same as building the calculated field as described above.

Hopefully, someone else will have a more efficient solution to the Mon-Sun requirement. Keith Wilhoit

[Tim Kiebert]

Re: Summarize Records by Date Range?

To change the week() function to return for Mon to Sun subtract 1 from the date like

dd = {20/07/2008}  'this is a Sunday

?week(dd)
= 30

?week(dd-1)
= 29

[Emerald IT]

Re: Summarize Records by Date Range?

Thank you both for your solutions. The first one works for now.