PivotTable Dynamically Selecting the Last Six Items

[SherryO]

I would like to code a Pivot Table report to refresh (the data is coming from Oracle, got the refreshing down) and then select the last six weeks in one of the PT fields. Does anyone know how to do that?
Thanks!

[XLGibbs]

How are you building your query to the Oracle datbase? If you are using External Data with MS Query to get the data...this can be quite easy...

You can set criteria in MS Query with the following, provided a regular date field is available in the source...

=dateadd(w,-6,now())

which will take NOW (Today's date) and go six weeks back ...

If you are not building the query yourself, and you want the pivot table to be filtered to certain dates, that is a little trickier, but can be done in VBA .

[SherryO]

I don't have access to the DB via MS Query. (Our Oracle install wasn't the swiftest in the book.:> ) So I only have the option of doing it once the data has been retreived in the Pivot Table. How can I do it in the PT where I go from a Week Ending date back six weeks? Our data is typically one week old when we get it, but sometimes more at month end.

[XLGibbs]

How is the date field itself structured in the pivot table...is it an actual date field or is it a text field with the date in there...makes a difference..

Also, is the data for the pivot table fed right into the pivot table, or would I be correct in assuming that since you have a dynamic range name already (per your reply in the other thread) there is a table that can be filtered?

My suggestion would be to filter the table itself into a new location, have your pivot table refer to that range instead...the filtering process can be done via code...and by creating a new set of data, the original would not be compromised.

[SherryO]

It's actually a column field and I believe it's an acutal date. I'm not using the dynamic named range in this PT, it's fed directly to the PT. It's structured with Refreshed Date in teh Page Field, FY and Week End in the column Fields, Employee and Project in the Row fields and Sum of Weekly % applied in the Data Field. The only one I need to set is the last six weeks in the Week End Field. Does that help?

[XLGibbs]

Here is what i need then and I get get some code for you that should do the tirck..

The sheet name where the pivot table resides
The name of the pivot table
The name of the field that contains the week-ending date

And I will post back with some code you can try ...

[XLGibbs]

Sub FilterDateField()
Dim pi As PivotItem ,ws As Worksheet , SixWeeksAgo As Date,PT As PivotField

Application.Screenupdating = False
Application.EnableEvents = False
SixWeeksAgo = Now() - 42

Set ws = ActiveSheet
Set PT = ws.PivotTables("PivotTable2").PivotFields("date")

For Each pi In PT.PivotItems

If pi < SixWeeksAgo Then pi.Visible = False
Next pi
Set ws = Nothing: Set PT = Nothing
Application.Screenupdating = True
Application.EnableEvents = True
End Sub



Change the sheet name, pivot table name and pivot field name accordingly. The above was tested on a date field named "Date" and works. Can only be run from the sheet which is currently active...so as long as you run it from the sheet containing pivot table...should be good to go..

[SherryO]

I'm getting a a type mismatch error. Does that mean that my date field is not really a date? Where can I check it since I don't have access to the source data? Thanks so much for your help.

[XLGibbs]

Yes, that could mean it is not a date...try this syntax in the meantime...but it would be helpful to know which line had the syntax error as it worked fine on my end (you are probably correct that field is not a date..

If datevalue(pi) < datevalue(SixWeeksAgo) Then pi.Visible = False


See if that works...I will check back in a bit, but I will hang with ya to get this resolved... :)

Ps...I didn't think of it before but the variable Pi is probably a bad choice for the pivot item alias...might want to swap the PI in the syntax for something else..

[SherryO]

The error was on this line.

If pi < SixWeeksAgo Then pi.Visible = False

Now the error is on this line. I changed pi to be PVI

If DateValue(PVI) < DateValue(SixWeeksAgo) Then PVI.Visible = False

It's still the type mismatch error

[XLGibbs]

How is the Weekending date displayed on the screen in the pivot table?

You also changed the "For each pi in ...." to "For each PVI..." correct?

[SherryO]

12/18/2005
This is how it's displayed. I apprecaite your patience:>

[XLGibbs]

No problem try it this way...forcing a few things that shouldn't be necessary to make the fields "match"


Sub FilterDateField()
Dim PVI As PivotItem ,ws As Worksheet , SixWeeksAgo As string,PT As PivotField

Application.Screenupdating = False
Application.EnableEvents = False
SixWeeksAgo = Format(Now() - 42,"mm/dd/yyyy")

Set ws = ActiveSheet
Set PT = ws.PivotTables("PivotTable2").PivotFields("date")

For Each PVI In PT.PivotItems
If datevalue(PVI) < datevalue(SixWeeksAgo) Then PVI.Visible = False
Next PVI

Set ws = Nothing: Set PT = Nothing
Application.Screenupdating = True
Application.EnableEvents = True
End Sub



We will change gears to try and forcefully make the "SixWeeksAgo" match by changing Sixweeks ago into a string, formatting the same, and the comparing the datevalues. Some extra steps in there to "force" things, but it may lead to postive result since it is not clear why it worked it my tests, and not so far on yours...

[SherryO]

I get the type mismatch on this:
If DateValue(PVI) < DateValue(SixWeeksAgo) Then
??? I'm stumped. I wish I could send you the file, but NDA...

[XLGibbs]

If you step through the code, and cursor over the variables as the "fill" you may be able to see what is happening to diagnose....particularly the PVI and SixWeeksAgo variables...

Did you make sure the names of the field, sheets and pivot table are correct in the code?

[SherryO]

Hi, I'm back to trying to figure this one out. Can you help to select only the last six items in a PivotTable? What about PivotItems.Count? I would appreciate any help. Thanks!