Group by weeks in a month

[jwaldner]

I am working on a cross tab where I need to group by weeks in a month. Just grouping by weeks is not good enough because the weeks at the start and end if the month usually fall in the next or previous months respectively.

Ideally I need the group header to show the first though the last day of the week for each week. [for September 2010 that is the 1st - 4th, not the 7 day grouping that is the default]



Any ideas out there

 

[lbass]

So there are multiple months in the record set? What do you want displayed as the column header? 1 to 5 and then resetting for each month?

-LB

 

[jwaldner]

My weeks are in the rows and I want to group by weeks in a month, not weeks in a year. so for September 2010 the groupings would be:
1st-4th
5th-11th
12th-18th
26th-30th

If you group by week, and don't care what month the weeks fall in you get:
8/29-9/4
5th-11th
12th-18th
26th - 10/2

I need data grouped by the first example.

 

[lbass]

Well, actually, the data WILL be grouped in the correct months if you use the weeks for the row and months as the columns. You can test this by adding the minimum and maximum of the date field as the crosstab summaries. The problem, I'm guessing, is that you don't want the results for one week to appear in two different columns, is that correct?

-LB

 

[jwaldner]

Almost correct, What goes across the top is another grouping for the purpose if this thread it can be thought of as a 'Customer' and the report is a transaction count for 5 'Top' customers. So the groups, just describe when the Transaction took place and the intersection is a count for the customer.

 

[lbass]

You didn't really answer my question. Do you care if it looks like this:
March April
3/28/2004 xxx xxx

Or must the data appear like this:
March April
3/28/2004 - 3/31/2004 xxx
4/1/2004 - 4/3/2004 xxx

-LB

 

[jwaldner]

Sorry, thought I did.
The data is like this:

M1 cust1 cust2 cust3 Total
wk1 6 1 2 9
wk2 2 2 2 6
wk3 3 3 2 7
wk4 1 2 2 5
M2 12 8 8 28
wk1
wk2
wk3
wk4

I need to group the data so that the totals for the month are accurate. If I group by week, the counts come from more than one month.

 

[lbass]

If you group by month and then by weeks, i.e., row#1 is month and row#2 is week in the crosstab expert, the results should be correct.

-LB

 

[jwaldner]

You are right, [Just confirmed] so the only thing thats off is the group description for the week. it shows dates from the next or previous month. The Math seems to be right though.

 

[lbass]

This should work for you. In the crosstab expert, highlight the {table.date} for Row#2 (week)->group options->options tab->customize group name->use a formula->x+2 and enter:

if {table.date}-dayofweek({table.date})+1 <
dateserial(year({table.date}),month({table.date}),1) then
totext(dateserial(year({table.date}),month({table.date}),1),"MM/dd/yyyy") + " - " +
totext({table.date}-dayofweek({table.date}) + 7,"MM/dd/yyyy")
else
if {table.date}-dayofweek({table.date}) + 7 >=
dateserial(year({table.date}),month({table.date})+1,1) then
totext({table.date}-dayofweek({table.date}) + 1,"MM/dd/yyyy") + " - " +
totext(dateserial(year({table.date}),month({table.date})+1,1)-1,"MM/dd/yyyy") else
totext({table.date}-dayofweek({table.date}) + 1,"MM/dd/yyyy") + " - " +
totext({table.date}-dayofweek({table.date}) + 7,"MM/dd/yyyy")

-LB

 

[jwaldner]

Wow thanks!