pivot table report filter grouping
hey guys i want to group the date of one of my pivot table fields. i know how to group it if its in the rows or columns place, but i don't know how to group it if it's in the report filter place. i've attached the picture below, it's showing me individual dates of dec and nov. i want to be able to choose "DEC" or "NOV" or whichever other date it is. could yall help me out? not sure if this is possible or not. thanks guys!!
(maybe i have to create one more column showing the month(A2) for example - but if i have to resort to that, how do i show "mmm yyyy" format from the list that the user selects from? i know i can format the cell to show "mmm yyyy" but that only shows that format after the user selects 1/12/2012 from the value list.
so in the end i used the text formula text(a3,"mmm yyyy") for the work around solution which gave me the right formatting, but how do i sort it in the value list as a sequential months instead of alphabetical order? thanks guys!!
(maybe i have to create one more column showing the month(A2) for example - but if i have to resort to that, how do i show "mmm yyyy" format from the list that the user selects from? i know i can format the cell to show "mmm yyyy" but that only shows that format after the user selects 1/12/2012 from the value list.
so in the end i used the text formula text(a3,"mmm yyyy") for the work around solution which gave me the right formatting, but how do i sort it in the value list as a sequential months instead of alphabetical order? thanks guys!!
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi developingprogrammer, I usually have another column in my data with the month end date for the period, and use that instead of using the grouping functions. However if group them first in the row field, then move it up to the page field, it seems to behave itself a bit better that way. You can use Pivot Table Tools menu > Group Field to group on Months & Years if you prefer
Hope that helps
...Terry
Hope that helps...Terry
When I'm faced with this sort of problem, I introduce integer values for yearmonth equal to Year * 100 + month (where month is one-based). EG January 2013 would be 201301. These values are easily converted back to a date if necessary. Year = int(yearmonth / 100), Month = yearmonth mod 100. date() and eomonth() easily get back to a floating point date values for comparison to other date values.
With this calculated field you should be able to filter as needed and sorting should be a breeze. It doesn't take long for end users to grasp the concept either. Although it may be somewhat foreign initially.
With this calculated field you should be able to filter as needed and sorting should be a breeze. It doesn't take long for end users to grasp the concept either. Although it may be somewhat foreign initially.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial