Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

pivot table report filter grouping

Posted on 2012-12-30
5
Medium Priority
?
334 Views
Last Modified: 2013-02-04
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!!value list shows 1 dec 2012 instead of just dec 2012
(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.work around adding a month year columnafter formatting the report filter cell
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!!
0
Comment
Question by:developingprogrammer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 16

Assisted Solution

by:terencino
terencino earned 1000 total points
ID: 38731977
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
Grouping dialogHope that helps
...Terry
0
 
LVL 2

Accepted Solution

by:
hanklm earned 1000 total points
ID: 38733663
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.
0
 

Author Comment

by:developingprogrammer
ID: 38777143
ok guys let me try it and get back to yall. so sorry for the super late response!!!!
0
 

Author Comment

by:developingprogrammer
ID: 38812928
will try this next week guys, sorry for the delay!!
0
 

Author Closing Comment

by:developingprogrammer
ID: 38853734
thanks guys for your help!! appreciate it!! = )))
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question