I just want to group my dates in a pivotchart by months or weeks to get
sales or weight sums by the month or week. Problem is that the chart won't
allow me to group the dates--it says that this type of data cannot be
grouped. why? why? why?

the dates in the cells also have time info:
i.e.
7/1/2004 9:53:07 AM

so when I put the Date field into the Category area of the chart it plots
every row down to the second! I'm lost and confused and searched the whole
newsgroup but can't find any postings of this same issue.

it seems like something common. can anyone help?

thanks,
kamal

I used data like this:

Date Item Income
31/12/04 10:00:00 Nuts 23.19
5/1/05 09:00:00 Bolts 35.23

etc

I set up the pivottable with pivotchart report with Date as the row
heading, Item as the column heading and Sum of Income in the Data area.
I then clicked on the Date field and used the Date drop-down menu and
Group to group by months, the resulting chart showed the months on the
category axis. I don't see why your data shouldn't work, just make
sure your date cells are formatted as dates and not text or something
peculiar.

Andrea Jones
www.stratatraining.co.uk


Kamal Hood wrote:
I just want to group my dates in a pivotchart by months or weeks to
get
sales or weight sums by the month or week. Problem is that the chart
won't
allow me to group the dates--it says that this type of data cannot be
grouped. why? why? why?

the dates in the cells also have time info:
i.e.
7/1/2004 9:53:07 AM

so when I put the Date field into the Category area of the chart it
plots
every row down to the second! I'm lost and confused and searched the
whole
newsgroup but can't find any postings of this same issue.

it seems like something common. can anyone help?

thanks,
kamal

This is a bug! Luckily I figured out a workaround but check this out. I am
using the PivotTable to summarize sales figures for transactions over the
last 6 months for our company. There are 64,000 rows of information in the
data table. If I select the whole data table as the data source for the
pivot table, then drag in the date column into the row area of the
PivotTable, it lists each date separately since the dates are recorded down
to the second. So, then I try to group the date field by month and I get and
error message "Cannot group this item".

Buuuuuuuuut, if I don't select all of the rows, and only select say a couple
of hundred...then maybe (i have to play around with the selection a lot)
when I go back to the PivotTable I can group by the month with no errors.
Then, I have to go back to the selection and reselect all 64,000 rows so
that they are included in the PivotTable (since I've already done the
grouping on the table things seem to work).

This sucks though. And it's a bug (I was a bug tester for many years). Does
anybody know what could be going wrong here?

thanks!
kamal


Thanks so much Andrea for trying this out. I don't know what is wrong.
wrote in message
oups.com...
I used data like this:

Date Item Income
31/12/04 10:00:00 Nuts 23.19
5/1/05 09:00:00 Bolts 35.23

etc

I set up the pivottable with pivotchart report with Date as the row
heading, Item as the column heading and Sum of Income in the Data area.
I then clicked on the Date field and used the Date drop-down menu and
Group to group by months, the resulting chart showed the months on the
category axis. I don't see why your data shouldn't work, just make
sure your date cells are formatted as dates and not text or something
peculiar.

Andrea Jones
www.stratatraining.co.uk


Kamal Hood wrote:
I just want to group my dates in a pivotchart by months or weeks to
get
sales or weight sums by the month or week. Problem is that the chart
won't
allow me to group the dates--it says that this type of data cannot be
grouped. why? why? why?

the dates in the cells also have time info:
i.e.
7/1/2004 9:53:07 AM

so when I put the Date field into the Category area of the chart it
plots
every row down to the second! I'm lost and confused and searched the
whole
newsgroup but can't find any postings of this same issue.

it seems like something common. can anyone help?

thanks,
kamal

Kamal -

I suspect there are some non numeric values in that column. When you do a subset,
you might not include a bad value, so grouping is okay. Once grouping is set up,
Excel might ignore the bad values and retain the grouping.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Kamal Hood wrote:

This is a bug! Luckily I figured out a workaround but check this out. I am
using the PivotTable to summarize sales figures for transactions over the
last 6 months for our company. There are 64,000 rows of information in the
data table. If I select the whole data table as the data source for the
pivot table, then drag in the date column into the row area of the
PivotTable, it lists each date separately since the dates are recorded down
to the second. So, then I try to group the date field by month and I get and
error message "Cannot group this item".

Buuuuuuuuut, if I don't select all of the rows, and only select say a couple
of hundred...then maybe (i have to play around with the selection a lot)
when I go back to the PivotTable I can group by the month with no errors.
Then, I have to go back to the selection and reselect all 64,000 rows so
that they are included in the PivotTable (since I've already done the
grouping on the table things seem to work).

This sucks though. And it's a bug (I was a bug tester for many years). Does
anybody know what could be going wrong here?

thanks!
kamal


Thanks so much Andrea for trying this out. I don't know what is wrong.
wrote in message
oups.com...

I used data like this:

Date Item Income
31/12/04 10:00:00 Nuts 23.19
5/1/05 09:00:00 Bolts 35.23

etc

I set up the pivottable with pivotchart report with Date as the row
heading, Item as the column heading and Sum of Income in the Data area.
I then clicked on the Date field and used the Date drop-down menu and
Group to group by months, the resulting chart showed the months on the
category axis. I don't see why your data shouldn't work, just make
sure your date cells are formatted as dates and not text or something
peculiar.

Andrea Jones
www.stratatraining.co.uk


Kamal Hood wrote:

I just want to group my dates in a pivotchart by months or weeks to

get

sales or weight sums by the month or week. Problem is that the chart

won't

allow me to group the dates--it says that this type of data cannot be
grouped. why? why? why?

the dates in the cells also have time info:
i.e.
7/1/2004 9:53:07 AM

so when I put the Date field into the Category area of the chart it

plots

every row down to the second! I'm lost and confused and searched the

whole

newsgroup but can't find any postings of this same issue.

it seems like something common. can anyone help?

thanks,
kamal

How about adding another column to your data and using the formula
=ISNUMBER(Ref) to find out if any of your dates are actually text or
something?

Andrea Jones
www.stratatraining.co.uk

Kamal Hood wrote:
This is a bug! Luckily I figured out a workaround but check this out.
I am
using the PivotTable to summarize sales figures for transactions over
the
last 6 months for our company. There are 64,000 rows of information
in the
data table. If I select the whole data table as the data source for
the
pivot table, then drag in the date column into the row area of the
PivotTable, it lists each date separately since the dates are
recorded down
to the second. So, then I try to group the date field by month and I
get and
error message "Cannot group this item".

Buuuuuuuuut, if I don't select all of the rows, and only select say a
couple
of hundred...then maybe (i have to play around with the selection a
lot)
when I go back to the PivotTable I can group by the month with no
errors.
Then, I have to go back to the selection and reselect all 64,000 rows
so
that they are included in the PivotTable (since I've already done the
grouping on the table things seem to work).

This sucks though. And it's a bug (I was a bug tester for many
years). Does
anybody know what could be going wrong here?

thanks!
kamal


Thanks so much Andrea for trying this out. I don't know what is
wrong.
wrote in message
oups.com...
I used data like this:

Date Item Income
31/12/04 10:00:00 Nuts 23.19
5/1/05 09:00:00 Bolts 35.23

etc

I set up the pivottable with pivotchart report with Date as the row
heading, Item as the column heading and Sum of Income in the Data
area.
I then clicked on the Date field and used the Date drop-down menu
and
Group to group by months, the resulting chart showed the months on
the
category axis. I don't see why your data shouldn't work, just make
sure your date cells are formatted as dates and not text or
something
peculiar.

Andrea Jones
www.stratatraining.co.uk


Kamal Hood wrote:
I just want to group my dates in a pivotchart by months or weeks
to
get
sales or weight sums by the month or week. Problem is that the
chart
won't
allow me to group the dates--it says that this type of data
cannot be
grouped. why? why? why?

the dates in the cells also have time info:
i.e.
7/1/2004 9:53:07 AM

so when I put the Date field into the Category area of the chart
it
plots
every row down to the second! I'm lost and confused and searched
the
whole
newsgroup but can't find any postings of this same issue.

it seems like something common. can anyone help?

thanks,
kamal