You first need to ungroup the items, add the calculated item, and then regroup the items. For more information about grouping, see About grouping data in a PivotTable at the end of this article. Here is a link to the page Count Unique Items In a pivot table, you may want to know how many unique customers placed an order for an item, instead of But the answers in that thread are all aimed at people who pivot-tabling on data imported to Excel, rather than against SSAS: excel date ssas grouping pivot-table share|improve this question asked have a peek here
You canâ€™t add a calculated item to an already grouped field. You may have to widen your column a little, to actually see whether your data is left aligned (text) or right aligned (numbers). As shown here, the City2 field is based on the City field, and is placed in the Rows area to group the selected cities. Share Share this post on Digg Del.icio.us Technorati Twitter Regards, Juan Pablo González http://www.juanpg.com Reply With Quote Feb 10th, 2004,06:21 PM #3 steveski Board Regular Join Date Feb 2004 Posts 55
It's usually due to a null value on the date field. Watch this video to see the steps for creating groups from text items. For a data model PivotTable, when you drag a date field with over one thousand rows of data from the Field List to the Rows or Columns areas, the Date field
can anyone here please pinpoint out to me so that i can rectify it? Backup copies of the file worked, but every time I went to the most recent, or copied the data from the most recent to the backup, the table messed up. Once I moved the field into the main body of the pivot table, I was able to group the dates. Pivot Table Not Grouping Dates Then Finish - right click for menu again -Refresh Data.Having said all this, sometimes Group still doesn't work even after you have corrected the data range!
Forum Board FAQ Forum Rules Guidelines for Forum Use FAQ Forum Actions Mark Forums Read Quick Links Today's Posts Search New Posts Zero Reply Posts Subscribed Threads MrExcel Consulting Advanced Search Excel Pivot Table Group Field Grayed Out To change a group label to something more meaningful, click it, > Field Settings, and in the Custom Name box, type the name you want. This, depending on how many extra rows you include, can end up increasing your file size by 100% or greater. http://www.ozgrid.com/forum/showthread.php?t=18319 Compare Excel | Excel Templates | DownloaderXL Pro Finance Templates & Add-In Bundle | NeuroXL Predictor | Construction Estimating Merge Excel The Easy Way | Trading Add-ins For Excel
To use different grouping in each pivot table, you'll need to create a separate pivot cache for each pivot table. Excel Cannot Group Columns Is there actually a way to make "Group Field" work on date dimensions in Pivot-Tables/SSAS? Regards Weasel Kind Regards Pesky Weasel "I have a plan so cunning, you could put a tail on it and call it a Weasel." EB Eagles may soar, but Weasels don't When you group selected items, you create a new field based on the field you are grouping.
Can you tell me some possible causes of this error? http://www.pcreview.co.uk/threads/pivot-table-grouping-of-dates-in-row-fields-will-not-work-why.2130727/ If you ungroup numeric or date and time fields, all grouping for that field is removed. Cannot Group That Selection Excel 2010 Share it with others Twitter Linked In Google Reddit StumbleUpon Posting Permissions You may not post new threads You may not post replies You may not post attachments You may not Cannot Group That Selection Excel 2013 Pivot Table Thanks.
David Thanks (0) Please login or register to join the discussion. navigate here Grouping Pivot Table Data. Tips:Â For a more compact PivotTable, you might want to create groups for all the other ungrouped items in the field. You can also use Data / Filter / Autofilter on the date field and any funny entries will usually stand out. Cannot Group That Selection Excel 2007 Pivot Table
LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear Mode Switch to Hybrid Mode BTW, it's best to start with the field that you want to group as a row field. -- C^2 Conrad Carlberg Excel Sales Forecasting for Dummies, Wiley, 2005 "Bill B"
Your name or email address: Do you already have an account? Excel Pivot Table Group By Month Not Working What should I do about this security issue? Ozgrid Retains the Rights to ALL Posts and Threads current community chat Stack Overflow Meta Stack Overflow your communities Sign up or log in to customize your list.
When I click "Group Selection" on my invoice dates, I just get the message "Cannot group that selection". April 12th - 18th: Free webinar, Mastering Pivot Tables -- Learn how to set up data and create a Pivot Table in less than 3 minutes. Can anyone confirm this? –SebTHU Aug 18 '15 at 11:13 add a comment| 1 Answer 1 active oldest votes up vote 0 down vote I was having the same problem. Cannot Group That Selection Pivot Table 2013 Then I brought in a column of dates from the ‘real’ data entry sheet, and now the grouping is not functional (“Cannot Group That Selection” error).
Once grouped together, you can drag the group to your Pivot Table and start your analysis. I have a Pivot Table based on the data entry sheet. Excel 2016 only: You can turn off time grouping in PivotTables (including data model PivotTables) and Pivot Charts by editing your registry. http://brsgrp.com/pivot-table/pivot-table-sum-not-working.html Your browser can't show this frame.
My interpretation of help on > this subject is that it should work. Video: Grouping Text Items The Grouping dialog box isn't available if you try to group items in a text field. If you ungroup a group of selected items, only the selected items are ungrouped. As shown below, the four cities are arranged under the new groups, Atlantic and Pacific.
The other common reason for not being able to group data is that you have both text and numeric data in the same field. so i try to change the date to month by following the tips given in excelzone. For example, suppose you have four cities in the City field: Boston, New York, Los Angeles, and Seattle. Guest, Sep 9, 2005 #1 Advertisements Conrad Carlberg Guest Hi Bill, I hate when this happens.
I have a Pivot Table based on the data entry sheet. Download the sample file for this video: StoreSales2012_2013.zip Your browser can't show this frame. For example when you group a field called SalesPerson, you create a new field called SalesPerson1. Then, rename a group, or add more items.
Grouping Dates by Week To group the items in a Date field by week Right-click on one of the dates in the pivot table. The entry in the Ending at box should be larger or later than the entry in the Starting at box. Video: Grouping by Date Adds Extra Items Watch this short video to see how to hide the text on the extra items that are created when you group a date field, If you've fixed your dates and you're still getting the same error, please feel free to reply.
Excel won't group on a field with a null. Group items by weeks first, making sure Days is the only time period selected.