How-to solve sorting problem in Pivot table

[perco754]

Hi Excel experts,

I have a .NET app that produces an Excel Pivot Table. One of the many existing fields contains a DATE. With the DATE column as a base I calculate the YEAR, QUARTER and MONTH according to trivial formulas. The QUARTER in particular is calulated either as a Calendar quarter (Q1 = month 1-3, Q2 = month 4-7 ...) OR as our companies specific Fiscal Year (Q1 = month 10-12, Q2 = month 1-3 ...). The calculation is correct and the result is a 1 for quarter #1, 2 for quarter #2 etc.
Now if I look in my Pivot result the Quarter field is sorted 1, 2, 3, 4 disregarding it it's a FY Year or not.

However, the customer ordering this application now wants the Pivot field to look like Q1, Q2, Q3, Q4. Ok, so I made a string in Excel using the TEXT function as: ="Q"&TEXT(MOD(CEILING(22+B2 -9-1;3)/3;4)+1;"0") where cell B2 contains the MONTH number. Now I got these result in all my Quarter cells. BUT in the PIVOT the sorting of these are now Q2, Q3, Q1, Q4 (since I'm using the FY Year formula).

I need ASAP help to solve this since the app is "going-live" next week and I'm totally out of solutions.

BR,
Peer

 

[perco754]

I forgot some important information.

I know how to solve this inside Excel by rightclicking the Quarter field in the PT choose Field Settings -> Advanced -> AutoSort options = Ascending.
Now I need to this within .NET through the Excel Interop Namespace.
I have an instance of the PivotField object:

Excel.PivotField pvtRow = ((Excel.PivotField)pvt.PivotFields("Quarter"));
pvtRow.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pvtRow.Caption = "Quarter";
pvtRow.ShowAllItems = true;
pvtRow.AutoSort(Excel.XlSortOrder.xlAscending, pvtRow.SourceName);

Now the last line is NOT working and I don't know how I should do it.

/P

 

[perco754]

SOLVED IT

Changing
pvtRow.AutoSort(Excel.XlSortOrder.xlAscending, pvtRow.SourceName);

into
pvtRow.AutoSort((int)Excel.XlSortOrder.xlAscending, pvtRow.SourceName);

did the trick

A simple cast to an INT made it all crystal clear, or?