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!
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!
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 .
If you have posted the same question at multiple forums, please read this IMPORTANT INFO.
Please use the thread tools to mark your thread Solved
Please review the Knowledge Base
for samples and solutions , or to submit your own!
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.
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.
If you have posted the same question at multiple forums, please read this IMPORTANT INFO.
Please use the thread tools to mark your thread Solved
Please review the Knowledge Base
for samples and solutions , or to submit your own!
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?
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 ...
If you have posted the same question at multiple forums, please read this IMPORTANT INFO.
Please use the thread tools to mark your thread Solved
Please review the Knowledge Base
for samples and solutions , or to submit your own!
[VBA]
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
[/VBA]
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..
If you have posted the same question at multiple forums, please read this IMPORTANT INFO.
Please use the thread tools to mark your thread Solved
Please review the Knowledge Base
for samples and solutions , or to submit your own!
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.
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..
[Vba]
If datevalue(pi) < datevalue(SixWeeksAgo) Then pi.Visible = False
[/vba]
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..
If you have posted the same question at multiple forums, please read this IMPORTANT INFO.
Please use the thread tools to mark your thread Solved
Please review the Knowledge Base
for samples and solutions , or to submit your own!
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
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?
If you have posted the same question at multiple forums, please read this IMPORTANT INFO.
Please use the thread tools to mark your thread Solved
Please review the Knowledge Base
for samples and solutions , or to submit your own!
12/18/2005
This is how it's displayed. I apprecaite your patience:>
No problem try it this way...forcing a few things that shouldn't be necessary to make the fields "match"
[VBA]
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
[/VBA]
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...
If you have posted the same question at multiple forums, please read this IMPORTANT INFO.
Please use the thread tools to mark your thread Solved
Please review the Knowledge Base
for samples and solutions , or to submit your own!
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...
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?
If you have posted the same question at multiple forums, please read this IMPORTANT INFO.
Please use the thread tools to mark your thread Solved
Please review the Knowledge Base
for samples and solutions , or to submit your own!
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!