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. 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 |
All times are GMT +1. The time now is 08:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com