Pivot Table Question

[mtapp]

I currently have a pivot table grouped by the year and month. It is pulling by calendar year (Jan-Dec)and I need it to bpull by our fiscal year. (Oct-Sept) Currently looks like this: 01/01/2011 - 12/31/2011

CY Jan Feb Mar etc
2012 50.0 40.0 25.0
2013 50.0 30.0 20.0
2014 40.0 10.0 20.0

I need it to look at the Event OUT DATE and group it from 10/01/2011 - 09/30/2012 and look like this:

FY OCT NOV DEC
2012 50.0 50.0 50.0
2013 40.0 40.0 40.0
2014 10.0 10.0 10.0

Any assistance would be greatly appreciated

 

[rdw72777]

Excel defaults to calendar year (obviously) so I'm not sure how it would know to go by your fiscal year, unless fiscal year and fiscal month were in your underlying data. Is fiscal year in your data, or did you just create that to show the desired results?

 

[mtapp]

I just created that to show what I was looking for. I might be able to pull the fiscal year out of the DB. I will give that a try. Thanks

 

[rdw72777]

Yeha, sorry this is a quandry with Excel.

You could always create Fiscal month and Fiscal year columns in your data using formulas.If the Month value is Oct/Nov/Dec, then FY = CY=1, otherwise its CY. you could setup a table and vlookup in values for fiscal month (Oct would be 1, Nov would be 2, and so on).

its not easy, I used to work at a Nov-Oct Fy company, annoying it was.

 

[mtapp]

My report goes out to 2029 and they currently do not have the fiscal years set-up to go out that far. I took your idea of the lookup table and it seems to work great. Thanks