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,855

    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
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    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.

  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
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,339

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

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