Sort Cross Tab by Month 1

[cglass1015]

I have a cross tab report showing sales data by month. The report is laided out like this:

Month
Profit Center Monthly Sales

There are 24 months worth of data in the cross tab. Everything works as it should except I need to sort the data by month rather than chronologically. In other words, I need August 2008 to be sorted just to the left of August 2007.

This way management can easily compare this year's sales for that month to last year's sales for the same month. I cannot figure out how to get the data sorted in this manner. I assume that a group sort formula can be written to achieve this, but I'm not sure how to do it.

Any help or suggestions would be appreciated.

 

[lbass]

You need to have your column #1 based on month, and column #2 on year.

-LB

 

[cglass1015]

Thanks for the suggestion. However this doesn't work. The sales data is entered in the database on a daily basis and is dated as such. The cross tab is summing and grouping by month. I added the date field twice in the columns set the first one to print for each month and the second one to print for each year. I end up with the following:

8/2006 9/2006
2006 2006
Profit Center <SalesData> <SalesData>

 

[lbass]

Sorry. You need to use a formula for column #1:

month({table.date})

Add this and then add the date field on change of year as column #2.

In group options, customize the month column name by using a formula:

monthname(month({table.date}))

-LB

 

[cglass1015]

That works, thanks for your help.

Chad

 

[JustinPorter]

This is exactly what I needed! Is it possible to have it show the month as well as the year?

 

[lbass]

If the month is column #1 and you have year as column #2, then no, you should not attempt to add the year to the month name. It might help to know your crosstab setup and why you want the year displayed with the month. Are you trying to eliminate the need for a separate year column?

-LB

 

[JustinPorter]

The report is a consolidated revenue report. I'm looking for columns like:

Jan-06 Jan-07 Jan-08 Jan-09 | Feb-06 Feb-07 etc
Members 5 25 98 45 45 48
Guests
etc

 

[lbass]

Insert the following formula as your column field:

totext(month({table.date}),"00")+"-"+totext(year({table.date}),"0000")

Then while it is highlighted in the crosstab expert->group options->options tab->customize name->use a formula->x+2 and enter:

monthname(month({table.date}),true)+ "-"+totext({table.date},"yy")

-LB