Pivot Tables and Fiscal Year

  • Thread starter Thread starter RE Miller
  • Start date Start date
R

RE Miller

When grouping dates by quarters in a pivot table, is there any way to modify
which months are considered Q1, Q2, etc? I'd like Q1 to reflect dates from
July-September rather than Jan-March. Is this possible?
 
I use a helper column in the data table (before the pivottable is built) to show
the year and quarter.

I use this formula to show the fiscal year and quarter:

="FY"&YEAR(A1)-(MONTH(A1)<#)&"-Q"&INT(1+MOD(MONTH(A1)-#,12)/3)
Where # represents the first month of the fiscal year.

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)
 
Hi RE Miller,
When grouping dates by quarters in a pivot table, is there any way to
modify
which months are considered Q1, Q2, etc? I'd like Q1 to reflect dates from
July-September rather than Jan-March. Is this possible?
Click to expand...

Excuse me for butting in late - that's what I do these days :)

Another way to do this is to add the following code to the ThisWorkbook
module.

This works because you can change the caption of grouped dates from 'Qtr1'
to 'Q3'. Changing the caption manually is a bit of a waste of time, because
the captions will revert every time the pivot table is refreshed. The code
captures the update event for all pivot tables in the workbook and checks
for a 'Quarters' field. If it finds the field, it then changes all the
captions to Fin Year quarters.

Not extensively tested, but should work ok.

Ed Ferrero
www.edferrero.com

'=================================================
Option Explicit
' IsUpdated is a global variable that lets us know
' if the Pivot Table Update event has been triggered by the user
' or by our code in this module
Private IsUpdated As Boolean

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target
As PivotTable)
If Not IsUpdated Then
If SetFiscalQtr(Target) Then
Application.StatusBar = "Fiscal Year Quarters Set"
IsUpdated = False
End If
End If
End Sub

Private Function SetFiscalQtr(pt As PivotTable) As Boolean
' Function to set Quarters From Excel default to fiscal year
' i.e. Q1 = Jul, Aug, Sep etc.
If FieldExists(pt, "Quarters") Then
IsUpdated = True
With pt.PivotFields("Quarters")
' could check to see if we have already
' renamed the quarters, or just error and keep
' going
On Error Resume Next
.PivotItems("Qtr3").Caption = "Q1"
.PivotItems("Qtr4").Caption = "Q2"
.PivotItems("Qtr1").Caption = "Q3"
.PivotItems("Qtr2").Caption = "Q4"
.AutoSort xlAscending, "Quarters"
' turn error checking back on
On Error GoTo 0
End With
SetFiscalQtr = True
Else
SetFiscalQtr = False
End If
End Function

Private Function FieldExists(pt As PivotTable, strField As String) As
Boolean
' checks if strField is a Pivot Field in the Pivot Table pt
' true if field exists
Dim fld As PivotField

FieldExists = False
For Each fld In pt.PivotFields
If fld.Name = strField Then
FieldExists = True
Exit Function
End If
Next
End Function
 
Back
Top