Display Date by Weeks

[lkiningh]

I have a request to display several totals by weeks. Here is the example:

Report run for February 2012 would look like:

#Accepted #Denied #Referred
Feb 1-5 2 4 2
Feb 8-12 5 1 4
Feb 15-19 4 4 2
Feb 22-26 5 3 5
Total: 41 16 12 13


Report run for February & March 2012 would look like:

#Accepted #Denied #Referred
Feb 1-5 2 4 2
Feb 8-12 5 1 4
Feb 15-19 4 4 2
Feb 22-26 5 3 5
Total: 41 16 12 13

Mar 1-2 2 4 2
Mar 5-9 3 7 3
Mar 12-16 9 5 2
Mar 19-23 5 4 1
Mar 26-30 1 4 2
Total: 54 20 24 10

Does anyone have any ideas on how to accomplish this? Thanks!

 

[lbass]

Insert a group on date(on change of month), and then a group on date on change of week. Remove the groupname for week and replace it with a formula:

totext(minimum({table.date},{table.date},"weekly"),"MMM d")+"-"+
totext(maximum({table.date},{table.date},"weekly"),"d")

This assumes that your data is already limited to days of week 2 to 6.

Then just insert summaries at the week and month group levels.

-LB

 

[lkiningh]

Thanks lbass! That is exactly what I needed. Sorry for the delay in responding. I've been out of the office unexpectedly. Thanks again!

 

[lkiningh]

lbass,

I have another question. I would like for the week group to be static. Right now it is only displaying the dates where there is data.

Example:
For the week of February 6th through 10th, I would like for it to show "Feb 6-10". But since there is only data for February 8th it is showing "Feb 8-8".
Another example is for the week of March 19th through 23rd. It is showing "Mar 21-23" but I would like for it to show "Mar 19-23" even though there is no data for March 19 & 20.

Is there any way around that?

Thanks.

lk

 

[lbass]

Use the following instead of the earlier formula I suggested:

totext({table.date}-dayofweek({table.date})+2,"MMM d")+"-"+
totext({table.date}-dayofweek({table.date})+6,"d")

-LB

 

[lkiningh]

That's great! Now I have one small issue. When the weeks cross months it is putting the days in the wrong month.

Example:

Jan 16-20
Jan 23-27
Jan 30-3
January Total

Feb 6-10
February Total

Feb 27-2
Mar 5-9
Mar 19-23
Mar 26-30
March Total


Jan 30-3 is falling in the January total
I would like for Jan 30 and 31 to be in the January total and Feb 1, 2, 3 to be in the February total.

Feb 27-2 is falling in the March total
I would like for Feb 27, 28, 29 to be in the February total and Mar 1, 2 to be in the March total.

Any ideas?

 

[lbass]

What exactly are you grouping (all groups)?

-LB

 

[lkiningh]

There are only 2 groups:

GH1 = date (on change of month)
GH2 = date (on change of week)

 

[lbass]

Try this instead of the earlier formula:

datevar st := {table.date}-dayofweek({table.date})+2;
datevar end := {table.date}-dayofweek({table.date})+6;
if day(end) < day(st) then
if month(st)=month({table.date}) then
end := dateserial(year(st), month(st)+1,1)-1 else
if month(st)<>month({table.date}) then
st := dateserial(year(st),month(st)+1,1);
if st=end then
totext(st,"MMM d") else
totext(st,"MMM d")+"-"+totext(end,"d")

-LB

 

[lkiningh]

Works like a charm! Thanks LB!