Closed Thread
Results 1 to 8 of 8

Pivot Sort Not Working as Expected

  1. #1
    Registered User
    Join Date
    07-05-2017
    Location
    Northeast
    MS-Off Ver
    2016
    Posts
    3

    Question Pivot Sort Not Working as Expected

    I am not able to sort the pivot table by a date field (Finish Date).

    I have tried:
    1) click the column and select SORT (oldest to newest)
    2) Added the date column to the VAlues (as a number), Click it and selected Sort
    3) Added a new column that is the same for all rows, and then selected --> Sort --> More Sort Options --> Ascending by Finish Date

    None of these worked for me. I know I am missing something basic.

    I attached a simplified version (with slicer).

    Any help would be appreciated !

    Thank you,
    John
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,687

    Re: Pivot Sort Not Working as Expected

    PivotTable child Row Field will only sort within context of it's parent.

    Since each Finish Date has it's own parent (start date, and intern Deliverable Leader). Sort operation on Finish Date will not work.

    To sort entire table based on Finish Date. You either need to move Finish Date to top of Row Field or above Start Date.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    07-05-2017
    Location
    Northeast
    MS-Off Ver
    2016
    Posts
    3

    Re: Pivot Sort Not Working as Expected

    Thank you !

    That would explain why I cannot get this to work.

    So, if I want to keep the same layout, I could add another column to the raw data, label it "Finish Date Sortable". Put a formula in this new column (=Finish Date ), then drag this new "finish date sortable" field into the pivot as the first row, sort it, then hide the column. Not super great, but it gets the job done.

    I tried a calculated column, but it looks like calculated columns only work with values.

    Thank you again !

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Pivot Sort Not Working as Expected

    you can try to play with Sort data in a PivotTable with sort by values in selected column
    Last edited by sandy666; 07-05-2017 at 03:53 PM.
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  5. #5
    Registered User
    Join Date
    07-05-2017
    Location
    Northeast
    MS-Off Ver
    2016
    Posts
    3

    Re: Pivot Sort Not Working as Expected

    Thank you sandy

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Pivot Sort Not Working as Expected

    You are welcome

  7. #7
    Registered User
    Join Date
    11-25-2020
    Location
    Toronto,Canada
    MS-Off Ver
    365
    Posts
    2

    Re: Pivot Sort Not Working as Expected

    Hi there,

    I have pivot table sorting problem. I am trying to sort descending the Market value which is in "Values" field which doesn't work. I am trying to sort asset from largest to smallest. I tried to put the market value in row field but It didn't work.

    I really appreciate your help!

    Thanks,
    E
    Attached Files Attached Files

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,692

    Re: Pivot Sort Not Working as Expected

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

Closed Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Power Pivot Help - SQL code working sort of?
    By lwilt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-16-2016, 12:09 PM
  2. [SOLVED] filldown not working as expected
    By dmcgov in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-20-2016, 10:50 AM
  3. [SOLVED] Pivot Table filter is not working as expected
    By tuph in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-20-2015, 09:20 PM
  4. [SOLVED] IF/ELSEIF not working as expected
    By amphinomos in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-12-2015, 10:00 AM
  5. COUNTIF() not working as expected?
    By Neely8 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-28-2015, 09:28 AM
  6. Date Sort in Pivot Table - Not working properly
    By claudiocustodio in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-12-2013, 04:30 PM
  7. Excel 2007 : custom sort in pivot table not working
    By bigh2odog in forum Excel General
    Replies: 0
    Last Post: 10-02-2009, 10:45 AM

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