Meilleur auteur de réponses

Date formats and sorting in 2012 Beta

Question

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.

Réponses

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

Toutes les réponses

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

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.