Filter Pivot Table Data by Range of Dates

[JAlbers]

Greetings All,

I have a Dashboard report that is generated by a macro. I was having the macro refresh the pivot data, then read each record and copy the ones that were between 0-180 days old. These copied records then served as the data for the dashboard reports and graphs.

The problem was twofold. First, the time it took to refresh the pivot table, and Second reading each record tended to get stalled and the macro would freeze up and not exit though the error trap.

Being new to VBA and for that matter to Pivot Tables, I don't see a way to tell the pivot table I only want records that are no more than 180 days old from right now without going in and ticking off check marks in the filter.

Does anyone have any suggestions? I'm stumped.

Thanks

Jacob
Excel 2003 & 2010

 

[cyrilbrd]

Hi,

re 180 days old.
Have you tried to filter the data from the pivot?
Although the filters are quite limited You could use the "contains" and type "0-180 days old" if this is what you need...

 

[JAlbers]

Hi Cyrilbrd
After looking at the filter options, I don't see any way to filter out the undesired records as the criteria is a calculation of KeeperDate>=(Now()-180).

I am looking at using a helper column to identify the keepers, then filtering on that, but it looks a bit convoluted and it still involves a lot of formulas and calculations to select and filter the records which is where my first attempt wants to stall.

Thanks

Jacob

 

[cyrilbrd]

Noted,
as for "KeeperDate>=(Now()-180)." Is "KeeperDate" a field?
have you tired the calculated fields?
here is a link explaining a bit about them.

link1

link2

 

[Jerry Sullivan]

I am looking at using a helper column to identify the keepers, then filtering on that, but it looks a bit convoluted and it still involves a lot of formulas and calculations to select and filter the records which is where my first attempt wants to stall.

Hi Jacob,

Adding a helper column is a fairly simple and effective way to do what you describe.

This thread might be of interest to you...
http://www.mrexcel.com/forum/showthread.php?p=3117260

 

[JAlbers]

Cyrilbrd Yes KeeperDate would be a field, here is the calculation I came up with to filter the records:
=IF(('KeeperDate'+180)>=(MAX('KeeperDate')),"Good","Bad")
but this returns a #VALUE error. Just using this formula against the data returns the results expected.

Jerry, Thanks for the link, that is what I was working towards, I just didn't want to have to add the helper if I could avoid it. Seems like you should be able to build a pivot using VBA that filtered in just the records you need.

I guess the question remains what does this do to performance? My routine originally evaluated the date on every record in the pivot table and copied the ones that were within the date range I was looking for, to a new table but this was kind of sketchy, sometimes it worked, sometimes it just bogged down and the program went to not responding.

I really appreciate everyone's help with this. It gets a bit frustrating for every small section of code to work then it just hangs up when they are all strung together.

Jacob

 

[Jerry Sullivan]

Since you are already using macros as part of your process, is there any reason not to use a Date Filter with the Criteria Between (today's date-180) and (today's date)?

You mention that it takes long to refresh the Pivots. How long does it take?

 

[JAlbers]

Jerry
There isn't any reason for not doing it one way over another, this was just the way I tried first and it actually seemed to work...at first.

As to how long it takes to refresh the pivots, that doesn't take long at all, the time is eaten up by the execution of the code which examines every record in the data set, compares the date to the target date that is set earlier in the macro (records 180 days old or younger) then copies those records into a new worksheet for further summarizing and formatting for the dashboard. This process takes about 2 seconds per record copied plus the time it takes to read all of the records it doesn't need. Well over 20 minutes for this part of the macro to run.

That is why I would like to have the records filtered on the front end so I could just copy the block instead of individual records.

Jacob

 

[Jerry Sullivan]

20 minutes?! Ouch!

I'm optimistic we could improve on that.

Stepping through each row of a large data set with VBA is relatively slow and should only be used if there are no better options.

If you'll post your code, I'll try to help. Based on the your last description, using the AdvancedFilter Method is an approach to consider.

 

[JAlbers]

Jerry

Thanks!
Here is the code I have been having problems with. I know that there has to be a better way of approaching this:

Sub OnMySix()
' Bring in last 6 month's data for trend analysis
On Error GoTo ErrorTrap
'
    datebreak = Now() - 180
    Application.ThisWorkbook.RefreshAll
    Sheets("Data Staging").Select
    Cells.Select 'Clears Staging Area of previous data
        Columns("A:AX").Select
        Selection.Delete
        Selection.UnMerge
'
Sheets("Data Staging").Select
    Cells.Select
    Selection.Delete Shift:=xlUp
    Sheets("Pivot Table").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
'
    Sheets("Pivot Table").Select  'This bit just sets up a heading row on the 
        Rows("3:3").Select           'Data Staging Sheet
        Selection.Copy
        Sheets("Data Staging").Select
        Range("A5").Select
        ActiveSheet.Paste
            Range("A5:P5").Select
                With Selection.Interior
                    .Pattern = xlNone
                    With Selection
                        .HorizontalAlignment = xlCenter
                        .Font.Bold = True
                    End With
                End With

'  This bit here is the killer
lrow = Worksheets("Pivot Table").Range("A" & Rows.Count).End(xlUp).Row
For i = 4 To lrow
    If Worksheets("Pivot Table").Range("A" & i).Value >= datebreak Then
    Worksheets("Pivot Table").Range("A" & i & ":P" & i).Copy _
        Worksheets("Data Staging").Range("A" & Rows.Count).End _
            (xlUp).Offset(1, 0)
    End If
Next i
'the rest basically summarizes this data and formats it into the 
'tables and graphs of the dashboard

I have just tried using a helper column and filtering on that and it seems to be working, but I haven't gotten the data copied out of the pivot table yet either.

Jacob
Excel 2010