changing year grouping in pivot table

[Becksicle]

Hello,

I have a pivot table that is basically saying how much business a sales
person has closed per quarter over an 18 month period. The data is coming
from an automatic export from our sales database, so I am unable to change my
source data. See example of pivot table:

A B
Year Qtr Columns C - G then have sales people's names with Sum of Amount
2006 Q3
2006 Q4
2007 Q1
2007 Q2
2007 Q3
2007 Q4

However, our FY runs from July to June. So actually 2006 Q3, is 06/07 Q1.

A B
Year Qtr Columns C - G then have sales people's names with Sum of Amount
0607 Q1
0607 Q2
0607 Q3
0607 Q4
0708 Q1
0708 Q2

Is it possible to customise the date grouping in a pivot table so that I can
specify July as my start of year and have pivot tables summarising by FY
rather than CY?

Thanks Rebecca

 

[Dave Peterson]

I don't think you can change this.

Can you insert a new column in your source data worksheet--if it is imported to
a worksheet????

I like this formula in general--if the fiscal year starts on the first of month
number #:
="FY"&YEAR(A1)-(MONTH(A1)<#)&"-Q"&INT(1+MOD(MONTH(A1)-#,12)/3)

So if the fiscal year starts on July 1st, then I'd use:
="FY"&YEAR(A1)-(MONTH(A1)<7)&"-Q"&INT(1+MOD(MONTH(A1)-7,12)/3)

Then you can pivot on that column.

 

[Don]

Can you have the pivot table do its thing, then convert the data after? I
have done pivot tables to feed into other reports so I can sum them / label
them different yet retaining the raw data? expecialy since you can not touch
the raw data?

 

[Becksicle]

Thanks for the feedback, Dave.
The formula's great, the data is automatically updated into my worksheet
though, and overwrites the previous copy, so everytime I refresh, I'd have to
re-add my FY date column with the formula, so although it will work, it's not
ideal, but it will come in handy for other things, so thanks a lot!

 

[Dave Peterson]

If it's something you do a lot, you may want to add the formula column and do
the pivottable via a macro.