locked
Date sorting in PowerPivot pivots RRS feed

  • Question

  • Hi All,

    I know there have been some other discussions about Dates issues with PowerPivot but I have not seen the answer I need to adding here.

    The core issue is that when Dates go from PowerPivot to an associated pivot table they seem to go as text strings rather than dates (doh).  As a result any sorting of dates is messed.

    I am able to overcome this by using a Format function to get a DateKey ... =(Format(Table[Date], "YYYYMMDD".  This produces a results that can then be sorted effectively.  So, Sept1, 2012 become 20120901

    The challenge is that this is not a great format for end users.  I have another field I produce that I call GoodDate.  This one is "DD-MMM-YY" and so produces 01-Sep-12.

    So ... my question:

    • first of all, is there a fix in the works to the core issue of dates being seen as text?
    • until then how would I show users GoodDate while sorting by DateKey?

    Thanx in advance!


    • Edited by GudOne Friday, October 12, 2012 4:22 PM
    Friday, October 12, 2012 4:22 PM

Answers

  • Here are a couple ways to hopefully achieve what you are looking for:

    1. create a calendar hierarchy (for example Year -> Month -> Date).  That will always force the individual dates into monthly buckets.  And then, using your desired format ("dd-MMM-yy"), you can get the Excel sorting that you're looking for. 

    2. If you don't want to use the hierarchy, just use an Ansi date format ("yyyy-MM-dd") that will allow Excel sorting to work ascending or descending.  And that should be a bit more user friendly than just the date key.

    Let me know if that works.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com



    Monday, October 15, 2012 3:37 AM
    Answerer

All replies

  • Hi GudOne -

    In PowerPivot2012 (v2), you can set a column to sort by another column from within the PowerPivot window.  With that, you can display a user-friendly text label like you want, but sort behind the scenes based on your datekey.

    More details here.

    Hope that helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Friday, October 12, 2012 5:58 PM
    Answerer
  • Hi Brent,

    Thank you for this.  I had seen that dialogue in some other posts but did not have it in my version of PowerPivot.  I since downloaded an update and voila there was the dialogue (to "sort by column").  Good reminder to keep products up-to-date.

    So ... I have taken the GoodDate and told it to sort by DateKey.  The problem is that is still does not seem to work.  It sorts fine in PowerPivot it self but not in the associated Excel Pivot.  When I first opened the pivot it was sorted correctly but when I work with it and resort it is still sorting as a text string (below).

    Sorry if I have missed something and thank you in advance for your help.

    Cheers - Lorne

    Row Labels
    31-Oct-11
    31-May-12
    31-Mar-12
    31-Jul-12
    31-Jan-12
    31-Dec-11
    31-Aug-12
    30-Sep-12
    30-Sep-11
    30-Oct-11
    30-Nov-11


    Friday, October 12, 2012 9:19 PM
  • Hi GudOne -

    Have a hunch on the cause.  Can you go to the sort options on the pivot's axis labels (row or column, wherever your dates are), and make sure the sort options are set to "More sort options" -> "Datasource Order".  That should keep Excel from overriding PowerPivot's sort order.

    Let me know if that works.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Friday, October 12, 2012 9:40 PM
    Answerer
  • OK, nearly there.  Thanx again Brent.

    I did what you suggested and it did work (nearly).  It is now sorting by date but I want it to be newest to oldest.  I have the PowerPivot sorted that way and I have the pivot set to "Data Sort Order" but the pivot is showing oldest to newest.

    If I go into the pivot and try to resort (newest to oldest) it reverts back to sorting by text (as it overrides the "data sort order" when I do that).

    Cheers - Lorne

    Saturday, October 13, 2012 5:11 PM
  • Here are a couple ways to hopefully achieve what you are looking for:

    1. create a calendar hierarchy (for example Year -> Month -> Date).  That will always force the individual dates into monthly buckets.  And then, using your desired format ("dd-MMM-yy"), you can get the Excel sorting that you're looking for. 

    2. If you don't want to use the hierarchy, just use an Ansi date format ("yyyy-MM-dd") that will allow Excel sorting to work ascending or descending.  And that should be a bit more user friendly than just the date key.

    Let me know if that works.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com



    Monday, October 15, 2012 3:37 AM
    Answerer
  • Hi,

    I went with your last suggestion Brent.  Still seems odd that the "data sort order" did not pick up the newest to oldest sort order but as you say the YYYY-MM-DD is sufficient.

    Thank you again.

    Cheers - Lorne

    Monday, October 15, 2012 2:27 PM