+ Reply to Thread
Results 1 to 4 of 4

Pivot table Top x filter issue

  1. #1
    Registered User
    Join Date
    06-10-2013
    Location
    Quebec
    MS-Off Ver
    Excel 2007
    Posts
    3

    Pivot table Top x filter issue

    I'm having problem with the top x filter. What I'd like to see is the top 5 product of 2012. All products have values for 2012 but sometime does'nt for previous years. The issue is product 2 from Germany. When I filter for top 5, this product should be third of five but simply disapear. Seems like excel is checking much more than the 2012 values... Tried to put 0 in empty cell in options with no luck. Same issue with 2007 or 2010 version of Excel.

    Default sort are:
    1) by year ascending
    2) by product value of 2012 column

    The attached workbook is a scale down version but show you exactly the problem.
    Many thanks!
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    04-22-2013
    Location
    The Netherlands
    MS-Off Ver
    Excel 2003/2007 & 2010
    Posts
    90

    Re: Pivot table Top x filter issue

    The problem is you are filtering on the field values, which includes values for all years. Filtering or sorting these values does so on the sum over all years.

    You could add an additional column in your data sheet with only values for the year 2012, include that in your pivot field and filter on that.

  3. #3
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: Pivot table Top x filter issue

    Hi

    If you take Years to the Report Filter Area and select just one year at a time, then you can do it.
    Take a look at the attached workbook
    Attached Files Attached Files
    --
    Regards
    Roger Govier
    Microsoft Excel MVP

  4. #4
    Registered User
    Join Date
    06-10-2013
    Location
    Quebec
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Pivot table Top x filter issue

    Thanks Marti and Roger for your suggestions but it wont do... I really need to see changes over the years and changing data source (pivot my query) would require to bulid PT at run time since I dont know exactly what years the users will ask for...(userform that build the query from 25 years, 200 country, thousands of products) What I'll do is put a button somewhere that export PT data in another workbook then delete in vba what's not needed...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1