grouping dates by week/month/etc. on cat. axis

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.
<[email protected]> wrote in message
news:[email protected]...
> 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.
> <[email protected]> wrote in message
> news:[email protected]...
>
>>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.
> <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >