Pivot Table Grouping Problem

[rn]

My pivot table has a date field with data for dates
starting 08/08/2003 to 31/03/04. When I group this field
according to months, the table shows January as the first
month. I was expecting it to be August.

Any help will be much appreciated.

Thanks.

 

[Debra Dalgleish]

If you group by Years and Months, August will be first.
If you group by Months only, January will be first.

 

[rn]

Thanks Debra.

Another thing the code you posted re: suppressing zeros in
pivot tables will it work for Excel 2000. I noticed you
mentioned the 2002 version.

 

[Debra Dalgleish]

That code only works in Excel 2002 or later versions.

 

[rn]

Are there any solution to this problem for Excel 2000?

Thanks.

 

[Debra Dalgleish]

You could check a specific column for zero values:

'====================================
Sub HideZeroItemColumn()
'hide items that contain zero values
'in specific column
'by Debra Dalgleish
Dim r As Integer
Dim i As Integer
Dim lbl As Integer
Dim col As Integer
Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField
Dim pi As PivotItem
Dim pd As Range
Dim str As String
Set pt = Sheets("Pivot").PivotTables(1)
Set pf = pt.PivotFields("Dept") 'row field
col = 4 'column to check for zero totals

For Each pi In pf.PivotItems
pi.Visible = True
Next pi

i = pf.PivotItems.Count
lbl = pf.PivotItems(1).LabelRange.Row

For r = i + lbl To lbl Step -1
str = Cells(r, 1).Value
If Cells(r, col).Value = 0 Then
pf.PivotItems(str).Visible = False
End If
Next r

End Sub
'====================================