Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Pivot Tables: Unable to Group and Show Detail
I am trying to group a column of dates by year and month, and I've done
it before and it's worked fine. But now it's saying "Cannot group on that Selection". I checked the data and it's in date format and there aren't any blank or null values. It seems like this is a bug because I've done it before with the same exact data. Have any of you had this problem? What am I doing wrong? Thanks! Lis |
#2
|
|||
|
|||
Is it possible that some of your dates are actually text that look like dates?
Save the file. Then, in a blank cell type =ISNUMBER(A1), where A1 represents one of your dates. If it returns FALSE, it is actually text and not a date. You can pull that formula down to test all of the dates. If your values are text, you can Group Manually by selecting all the dates you want to Group and choosing Data | Group and Outline | Group. Alternatively you'll need to convert the text to dates. You would likely want to do this in the data source, if possible. The method of this may vary slightly depending on where the data is stored. tj " wrote: I am trying to group a column of dates by year and month, and I've done it before and it's worked fine. But now it's saying "Cannot group on that Selection". I checked the data and it's in date format and there aren't any blank or null values. It seems like this is a bug because I've done it before with the same exact data. Have any of you had this problem? What am I doing wrong? Thanks! Lis |
#3
|
|||
|
|||
I tried the ISNUMBER formula and they all came back as TRUE, so that's
not it. I know for sure that they are stored as dates, so that's why this is so weird to me. Thanks anyway - I appreciate your response. :-) tjtjjtjt wrote: Is it possible that some of your dates are actually text that look like dates? Save the file. Then, in a blank cell type =ISNUMBER(A1), where A1 represents one of your dates. If it returns FALSE, it is actually text and not a date. You can pull that formula down to test all of the dates. If your values are text, you can Group Manually by selecting all the dates you want to Group and choosing Data | Group and Outline | Group. Alternatively you'll need to convert the text to dates. You would likely want to do this in the data source, if possible. The method of this may vary slightly depending on where the data is stored. tj " wrote: I am trying to group a column of dates by year and month, and I've done it before and it's worked fine. But now it's saying "Cannot group on that Selection". I checked the data and it's in date format and there aren't any blank or null values. It seems like this is a bug because I've done it before with the same exact data. Have any of you had this problem? What am I doing wrong? Thanks! Lis |
#4
|
|||
|
|||
One more idea. Do you have any Calculated Items in your PivotTable? If so,
you can't group. tj " wrote: I tried the ISNUMBER formula and they all came back as TRUE, so that's not it. I know for sure that they are stored as dates, so that's why this is so weird to me. Thanks anyway - I appreciate your response. :-) tjtjjtjt wrote: Is it possible that some of your dates are actually text that look like dates? Save the file. Then, in a blank cell type =ISNUMBER(A1), where A1 represents one of your dates. If it returns FALSE, it is actually text and not a date. You can pull that formula down to test all of the dates. If your values are text, you can Group Manually by selecting all the dates you want to Group and choosing Data | Group and Outline | Group. Alternatively you'll need to convert the text to dates. You would likely want to do this in the data source, if possible. The method of this may vary slightly depending on where the data is stored. tj " wrote: I am trying to group a column of dates by year and month, and I've done it before and it's worked fine. But now it's saying "Cannot group on that Selection". I checked the data and it's in date format and there aren't any blank or null values. It seems like this is a bug because I've done it before with the same exact data. Have any of you had this problem? What am I doing wrong? Thanks! Lis |
#5
|
|||
|
|||
What's strange is it's working now. I didn't do anything differently.
I deleted the sheet that the pivot table was on and just started over and it's working fine. I think it might be a bug, not sure. I do have calculated items in my pivot table so it seems to be fine. I just don't know... Thanks for all of your help! |
#6
|
|||
|
|||
Curiosity point:
Do you have Calculated Items or Calculated Fields? tj " wrote: What's strange is it's working now. I didn't do anything differently. I deleted the sheet that the pivot table was on and just started over and it's working fine. I think it might be a bug, not sure. I do have calculated items in my pivot table so it seems to be fine. I just don't know... Thanks for all of your help! |
#7
|
|||
|
|||
There may be a grouped field left over from the previous time that you
grouped the data. Check the field list, to see if there's a second copy of the date field, e.g. Date2. If there is, add it to the row area, and ungroup it. Then, you should be able to group the date field again. wrote: I am trying to group a column of dates by year and month, and I've done it before and it's worked fine. But now it's saying "Cannot group on that Selection". I checked the data and it's in date format and there aren't any blank or null values. It seems like this is a bug because I've done it before with the same exact data. Have any of you had this problem? What am I doing wrong? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table - Group by Month - Show Items with no data | Excel Discussion (Misc queries) |