none
Date formats and sorting in 2012 Beta

    Întrebare

  • I'm new to Pivotpower and trying to convert a massive excel based pivot table sheet over. I have installed the 2012 Beta as lead to beleive issues with sorting by date had been fixed, but seems not.  My date a contains a date field "registration date" which is recognised by powerpivot as a date.  I have 2 problems.

    1. As far as I can tell I cannot format this date by the very common mmm-yy as it isn't one of the load of obscure formats that you can choose. Did i miss something?

    2. I want to sort my dates in reverse order in my pivot table where registration date is my row label.  There still only seems to be a Z to A text type sorting which is utterly useless. Again did I miss something?

    Reading round various forums there have bene so many compalints about the handling of dates in the 2008 release I'm surprised it hasn't been resolved in this release. Hopefully I'm just looking in the wrong place.

    Thansk for any advice. Mike

    10 aprilie 2012 15:20

Răspunsuri

  • 1. As far as I can tell I cannot format this date by the very common mmm-yy as it isn't one of the load of obscure formats that you can choose. Did i miss something?

    if the format you want is not in the default list (and the format you are after is a month format, not really a date format) you can always create your own calculated column.

    eg. MonthName := Format([DateValue], "mmm-yy")

    2. I want to sort my dates in reverse order in my pivot table where registration date is my row label.  There still only seems to be a Z to A text type sorting which is utterly useless. Again did I miss something?

    If you click the Z-A sort buttons in Excel it will only sort by the text, what you can do in PowerPivot v2 is to specify a default sort order based on another column by clicking on the "Sort by Column" button. So if you want your months in reverse order by default you can create a calculated column like the following and set that as the sort by column for MonthName.

    MonthSort := INT(FORMAT([DateValue], "YYYYMM")) * -1

    If you want to dynamically switch the sort order then you may need to compromise and use a format like YYYY-MM instead of MMM-yy


    http://darren.gosbell.com - please mark correct answers

    11 aprilie 2012 04:14
    Membru care oferă răspunsuri

Toate mesajele

  • 1. As far as I can tell I cannot format this date by the very common mmm-yy as it isn't one of the load of obscure formats that you can choose. Did i miss something?

    if the format you want is not in the default list (and the format you are after is a month format, not really a date format) you can always create your own calculated column.

    eg. MonthName := Format([DateValue], "mmm-yy")

    2. I want to sort my dates in reverse order in my pivot table where registration date is my row label.  There still only seems to be a Z to A text type sorting which is utterly useless. Again did I miss something?

    If you click the Z-A sort buttons in Excel it will only sort by the text, what you can do in PowerPivot v2 is to specify a default sort order based on another column by clicking on the "Sort by Column" button. So if you want your months in reverse order by default you can create a calculated column like the following and set that as the sort by column for MonthName.

    MonthSort := INT(FORMAT([DateValue], "YYYYMM")) * -1

    If you want to dynamically switch the sort order then you may need to compromise and use a format like YYYY-MM instead of MMM-yy


    http://darren.gosbell.com - please mark correct answers

    11 aprilie 2012 04:14
    Membru care oferă răspunsuri
  • I already have so many columns it seems ridiculous to have yet another column just because the format isn't available as a default!!! I'll just live with the long format.

    I'm still struggling with the date thing. I have a column called "Reg Month" with the formula

    =date(year(users[Registration date]),month(users[Registration date]),1)

    Where registration date is in the format dd/mm/yyy hh:mm so anything in January comes out as 1/1/2012. I format that using the "handy" mmmm yy giving me January 2012. I can sort "Reg Month" oldest-newest or newest-oldest no problem.  Doesn't the sort by column just allow me to sort "Reg Month" by some other system?  Doesn't seem I need that as works perfectly in powerpivot window.  the problems only start when I use "Reg Month" as a row label in a pivot table and now its only Z-A. 

    12 aprilie 2012 16:23
  • please refer the given link for your reference.

    http://boardreader.com/thread/Problems_installing_2012_Beta_version_2fd13__29ec70af-875e-4527-b968-350e73922838.html

    2 mai 2012 10:44
  • please refer the given link for your reference.

    http://boardreader.com/thread/Problems_installing_2012_Beta_version_2fd13__29ec70af-875e-4527-b968-350e73922838.html


    Nanurahi, we don't think this link relates to the thread. Thanks!

    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    22 august 2013 18:49
    Proprietar