Can't Add DATE PivotItems (VBA)

[bill9376]

'Cut and paste this code into the WORKBOOK_OPEN of a blank workbook, save and reopen,
'for a demo of the following problem:
'''''''''''''''''''''''''''''''''''''
'I need a pivot field ("month") whose 12 items span a year,
'regardless of whether each month is represented in the source data.
'To do this I loop through the PIVOTITEMS to add months that are missing,
'and, though not shown here, to hide months outside my year.
'But I can't get EXCEL to recognize that the new items are DATEs.
'They are not subject to number format and sort incorrectly.
'PIVOTITEMS.ADD is documented as taking a string for an argument.
'Does that mean it's not possible?
''''''''''''''''''''''''''''''''''
Dim PT As PivotTable
Dim PC As PivotCache
Dim Sh1 As Worksheet
Dim Sh2 As Worksheet
Dim PFdate As PivotField
Dim MonthItems(0 To 11) As PivotItem
Dim I As Long
Dim FromDate As Date
Dim ToDate As Date
Dim NewDate As Date
Dim PI As PivotItem
''''''''''''''''''''''''
Set Sh1 = ThisWorkbook.Worksheets("Sheet1")
Set Sh2 = ThisWorkbook.Worksheets("Sheet2")
'''''''''''''''''''''''''''''''''''''''''''
'build source data in sheet1, like:
'DATE NUMBER
'1/1/08 1
'3/1/08 1
'...
'11/1/08 1
'''''''''''''
Sh1.Cells.Clear
NewDate = "1/1/08"
Sh1.Cells(1, 1) = "DATE"
Sh1.Cells(1, 2) = "NUMBER"
For I = 2 To 7
Sh1.Cells(I, 1) = NewDate
Sh1.Cells(I, 2) = 1
NewDate = DateAdd("m", 2, NewDate)
Next I
Sh1.Cells.HorizontalAlignment = xlHAlignCenter
Sh1.Activate
MsgBox "This is the pivot source."
'''''''''''''''''''''''''''''''''''

Sh1.Cells(1, 1).Select
Sh2.Cells.Clear
Set PC = ThisWorkbook.PivotCaches.Add(xlDatabase, ActiveCell.CurrentRegion)
Set PT = PC.CreatePivotTable(Sh2.Cells(1, 1), "myPivot")
PT.AddFields ColumnFields:="date"
PT.AddDataField PT.PivotFields("number"), "Totals", xlSum
Set PFdate = PT.PivotFields("date")
PFdate.NumberFormat = "mmm-yy"
Sh2.Activate
MsgBox "This is pivot table before adding pivot items."
'''''''''''''''''''''''''''''''''''''''''''''''''''''''

'define domain:
'''''''''''''''
FromDate = "2008-01-01"
ToDate = DateAdd("m", 11, FromDate)

'fill in PI array with corresponding date items from source:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For Each PI In PFdate.PivotItems
If PI >= FromDate And PI <= ToDate Then
Set MonthItems(DateDiff("m", FromDate, PI)) = PI
End If
Next PI

'add missing months to PI collection:
'''''''''''''''''''''''''''''''''''''
For I = 0 To 11
If MonthItems(I) Is Nothing Then
NewDate = DateAdd("m", I, FromDate)
PFdate.PivotItems.Add NewDate
End If
Next I

PFdate.AutoSort xlAscending, PFdate.SourceName
PFdate.ShowAllItems = True

MsgBox "This is pivot after adding pivot items:" _
& vbCr & "The lost formatting and sorting suggest that the additions" _
& vbCr & "are treated as character strings, not dates."

 

[chergh]

I don't think you actually need to add months which have no data.

You want to use the ShowAllItems property to achieve what you want, whihc should be something like this:

thisworkbook.worksheets("Sheet1").PivotTables("PivotTable1").PivotFields("Date_Field").ShowAllItems = True

When I've used this in the past even if there isn't data for months it will still show that month and if your date field is grouped on month and year it will show all the months for that year even if they are in the future.

 

[bill9376]

Actually I think you do. I tried grouping and it did not show months for which there was no data. Maybe it did it wrong? Also you don't get the control over grouping headings that you do with pivot items. Anyway I think I found a workaround, which I will publish if it pans out. Thanks for responding.

 

[chergh]

Simply by grouping you won't get all months but if you add the code listed above it should show all months even if there is no data.

This can also be done double clicking on the date field on the pivot table and then checking the box in the bottom left hand corner that says "Show items with no data".

This image should help

 

[namliam]

Cherg, but that still doesnt show "January" if there is no data for Januari.

Taking a string means taking a string.

Have you tried adding your dummy data to your source table?

 

[chergh]

It does show January if there is no data for January

Here's my pivotable without "Show items with no data" checked

Here's my pivottable with "Show items with no data" checked.

As you can see all the months for each year appear in the later table. Give it a try yourself.

 

[namliam]

But what if you ONLY select data from 2008? The months will not show up...
You need to have somewhere data in your original table for it to show up in your cross table.

 

[chergh]

Here is my pivottable with only data for Jan and Feb of 2008 and "Show items with no data" checked.

Pivottables when working with dates and grouped by month and year will show all months regardless if there is any data for them, when you check the show items with no data.

 

[chergh]

Heres a spreadsheet with an example.

 

[bill9376]

I think I've got a workaround, but it's not seed data, which would upset functions like count. Programmatically construct a range of intended pivotitems, build a p-cache on it, then change the SOURCEDATA of the cache to the report data and refresh the table. I've got a working prototype. I'm surprised no one thinks there's a design problem with PIVOTITEMS.ADD !