grouping on cross tab dates

[mdj3366]

I need to be able to group by year for years up to 2009, but by month if the year is 2009, in a cross tab. I am using the sql expression created in this thread 767-1462006 to group. Thanks.

 

[lbass]

Create a formula, assuming your SQL expression is called {%mindate}:

if year({%mindate}) <> year(currentdate) then
date(year({%mindate}),1,1) else
date(year({%mindate}),month({%mindate}),1)

Add this as your column field in the crosstab and then click on group options->options tab->customize group name->use a formula->x+2 and enter:

if year({%mindate}) <> year(currentdate) then
totext(year({%mindate}),0,"") else
monthname(month({%mindate}),true)+" "+totext(year({%mindate}),0,"")

Note that when you reference a thread, if you leave no spaces between the word "thread" and the number, it will create a link, like this:

thread767-1462006

-LB

 

[mdj3366]

Hi LB,

I think i should have given a little more information...the cross tab column is being used to group at company level, the rows by %mindate and the summary is a count of the number of people in each state. I changed the grouping on the rows to your formula and did as suggested for the group option but it did not work.

 

[lbass]

It should have worked equally well as a row, so you will have to explain how you implemented my suggestion and also explain in what sense it did not work, i.e., what results you got instead.

-LB

 

[mdj3366]

I created the formula you suggested and the group option as well, however the group for months in 2009 that should have occured did not. The cross tab is still grouped entirely by year.

 

[lbass]

I tested this and it works. You should be adding the formula as your row field, and while it is highlighted in the crosstab expert, click on "group options" and proceed from there. Also make sure in the group options that it is set to print on change of day.

-LB

 

[mdj3366]

It does work. Thanks LB for your past and present support!!