Change pivot page filter based upon two cell values (dates from and to)

[Victor70]

Dear experts,

I saw solutions to update the pivot page filter based upon a cell value via a macro (see the link).
http://www.excelforum.com/excel-prog...ell-value.html

Is there a way to apply a from-to criteria for the pivot page filter to get the data for specific time interval? I was unable to modify the referenced code being unsure if pivot page filter supports complex criteria at all.

Thanks a lot for any thoughts.

This duplicates the same question at http://www.excelforum.com/excel-gen...d-upon-two-cell-values-dates-from-and-to.html No responses there so far...

 

[Jerry Sullivan]

Hi Victor and Welcome to the Board,

There's a PivotFilters Object Collection that can be used to apply a criteria filter to RowFields,
ColumnFields and Data Fields; however that doesn't work for PageFields.

Instead, you could use a function like the one below to step through the PivotItems
and make Visible those items in the To...From date range.

Public Function Filter_PivotField_by_Date_Range(pvtField As PivotField, _
        dtFrom As Date, dtTo As Date)
    Dim bTemp As Boolean, i As Long
    Dim dtTemp As Date, sItem1 As String
    
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    With pvtField
        .Parent.ManualUpdate = True
        For i = 1 To .PivotItems.Count
            dtTemp = .PivotItems(i)
            bTemp = (dtTemp >= dtFrom) And _
                (dtTemp <= dtTo)
            If bTemp Then
                sItem1 = .PivotItems(i)
                Exit For
            End If
        Next i
        If sItem1 = "" Then
            MsgBox "No items are within the specified dates."
            Exit Function
        End If
        If .Orientation = xlPageField Then .EnableMultiplePageItems = True
        .PivotItems(sItem1).Visible = True
        For i = 1 To .PivotItems.Count
            dtTemp = .PivotItems(i)
            If .PivotItems(i).Visible <> _
                ((dtTemp >= dtFrom) And (dtTemp <= dtTo)) Then
                .PivotItems(i).Visible = Not .PivotItems(i).Visible
            End If
        Next i
    End With
    
    pvtField.Parent.ManualUpdate = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Function

Here's and example of how a Sub might call the Function.

Sub Test_Filter_Date_Range()
    Dim dtFrom As Date, dtTo As Date
    Dim PT As PivotTable
    
    With Sheets("Sheet1")
        Set PT = .PivotTables("PivotTable1")
        dtFrom = .Range("B1")
        dtTo = .Range("B2")
    End With
    
    Call Filter_PivotField_by_Date_Range( _
        PT.PivotFields("Date"), dtFrom, dtTo)
End Sub

 

[Victor70]

Hi, Jerry,

Thank you very much for your input. I was thinking in the similar direction but thought to create formulas that would hide everything that did not fit the condition. This would, however, require quite a lot of calculations.

I will work with your function.

Thanks again!

Hi Victor and Welcome to the Board,

There's a PivotFilters Object Collection that can be used to apply a criteria filter to RowFields,
ColumnFields and Data Fields; however that doesn't work for PageFields.

Instead, you could use a function like the one below to step through the PivotItems
and make Visible those items in the To...From date range.

Public Function Filter_PivotField_by_Date_Range(pvtField As PivotField, _
        dtFrom As Date, dtTo As Date)
    Dim bTemp As Boolean, i As Long
    Dim dtTemp As Date, sItem1 As String
    
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    With pvtField
        .Parent.ManualUpdate = True
        For i = 1 To .PivotItems.Count
            dtTemp = .PivotItems(i)
            bTemp = (dtTemp >= dtFrom) And _
                (dtTemp <= dtTo)
            If bTemp Then
                sItem1 = .PivotItems(i)
                Exit For
            End If
        Next i
        If sItem1 = "" Then
            MsgBox "No items are within the specified dates."
            Exit Function
        End If
        If .Orientation = xlPageField Then .EnableMultiplePageItems = True
        .PivotItems(sItem1).Visible = True
        For i = 1 To .PivotItems.Count
            dtTemp = .PivotItems(i)
            If .PivotItems(i).Visible <> _
                ((dtTemp >= dtFrom) And (dtTemp <= dtTo)) Then
                .PivotItems(i).Visible = Not .PivotItems(i).Visible
            End If
        Next i
    End With
    
    pvtField.Parent.ManualUpdate = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Function

Here's and example of how a Sub might call the Function.

Sub Test_Filter_Date_Range()
    Dim dtFrom As Date, dtTo As Date
    Dim PT As PivotTable
    
    With Sheets("Sheet1")
        Set PT = .PivotTables("PivotTable1")
        dtFrom = .Range("B1")
        dtTo = .Range("B2")
    End With
    
    Call Filter_PivotField_by_Date_Range( _
        PT.PivotFields("Date"), dtFrom, dtTo)
End Sub

 

[Jerry Sullivan]

Hi, Jerry,

Thank you very much for your input. I was thinking in the similar direction but thought to create formulas that would hide everything that did not fit the condition. This would, however, require quite a lot of calculations.

I will work with your function.

Thanks again!

You're welcome.

I appreciate that you identified this as a cross-post.

Don't forget to update the thread on the other site to let other users know there's been a solution suggested.

 

[maheshrk81]

Thanks a lot for your reply.
But i need the pivot should select multiple dates.
In my Sheet1, i will mention From Date and To Date in Cell(M4 and N4).
And in my control sheet, i have a pivot table(Pivottable1), Now this pivot table should change its Sales date according to Sheet1 From Date and To Date which is in Cell(M4 and N4).

Please help.

Mahi.

 

[Jerry Sullivan]

Thanks a lot for your reply.
But i need the pivot should select multiple dates.
In my Sheet1, i will mention From Date and To Date in Cell(M4 and N4).
And in my control sheet, i have a pivot table(Pivottable1), Now this pivot table should change its Sales date according to Sheet1 From Date and To Date which is in Cell(M4 and N4).

Please help.

Mahi.

Mahi,
I've responded to your question in the original thread you started.
Let's continue our discussion there.

http://www.mrexcel.com/forum/showthread.php?p=3017978#post3017978

 

[arvindkrkaushik]

Hi All,

I am not able to get correct data from pivot table based on two dates criteria...pls help ASAP....

Many thanks in advance...

 

[arvindkrkaushik]

Hi,

I am sorry to say but this code is not working fine for me. this showing all records....pls help...

Hi, Jerry,

Thank you very much for your input. I was thinking in the similar direction but thought to create formulas that would hide everything that did not fit the condition. This would, however, require quite a lot of calculations.

I will work with your function.

Thanks again!

 

[Jerry Sullivan]

Hi arvindkrkaushik,

Is the field with the dates that you want to filter in the Reports Filter area of the PivotTable or the Row Labels area?

 

[arvindkrkaushik]

Hi,

This is pivot page filed and i am using Excel 2007.

Also my data contains some blank rows, it also shows (blank) option in pivot page filter. I have also changed the date format for this field to dd-mm-yy, however this is showing some records in the range but not all, like if my date range is from 1 May 2012 to 31 May 2012 and the data contains in sheet is like -- 1 April 2012to 1 June 2012 data and order of data is like 1 may 2012, 2 may, 3 may, 5, 7, 9 May 2012 and so on. Now when i run your vb code this shows the data up to 1 may 2012 to 3 may 2012 only, however this should show all records from 1 may to 31 may 2012. (i think it stops because the date in data is 3 may then 5 may....4 may 2012 is not there..please resolve this)


Note: I have changed some lines in this code: Also this code is working fine for me if there are continuous dates (like 1,2,3,4,5,6, and so on) with no (blank) date....

Sub Test_Filter_Date_Range()
Dim dtFrom As Date, dtTo As Date
Dim PT As PivotTable
With Sheets("Main Menu")
dtFrom = .Range("c3")
dtTo = .Range("e3")
End With

For Each PT In ActiveSheet.PivotTables

mypvt = PT
With Sheets("Vendor Pivots")
Set PT = .PivotTables(mypvt)
MsgBox PT.PageFields.Count
For i = 1 To PT.PageFields.Count
getFld = PT.PageFields(i)
If getFld = "Created Date1" Or getFld = "Joined On1" Or getFld = "Offer Pending1" Or getFld = "Offer Made1" Or getFld = "HAT Test - Schedule1" Then
Exit For
End If

Next
End With
'n1:
Call Filter_PivotField_by_Date_Range( _
PT.PivotFields(getFld), dtFrom, dtTo)

Next
End Sub



Public Function Filter_PivotField_by_Date_Range(pvtField As PivotField, _
dtFrom As Date, dtTo As Date)
Dim bTemp As Boolean, i As Long
Dim dtTemp As Date, sItem1 As String

On Error Resume Next

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With pvtField

.Parent.ManualUpdate = True
For i = 1 To .PivotItems.Count
dtTemp = .PivotItems(i)
bTemp = (dtTemp >= dtFrom) And _
(dtTemp <= dtTo)
If bTemp Then
sItem1 = .PivotItems(i)
Exit For
End If
Next i
If sItem1 = "" Then
MsgBox "No items are within the specified dates."
Exit Function
End If
If .Orientation = xlPageField Then .EnableMultiplePageItems = True

.PivotItems(sItem1).Visible = True
For i = 1 To .PivotItems.Count

If .PivotItems(i).Value = dtFrom Then
If dtTo < dtFrom Then
.PivotItems(i).Visible = False
'MsgBox .PivotItems(i).Value
dtFrom = dtFrom + 1
Else

.PivotItems(i).Visible = True
dtFrom = dtFrom + 1
End If
Else
'MsgBox .PivotItems(i).Value
.PivotItems(i).Visible = False
'dtFrom = dtFrom + 1

End If

Next i
End With

pvtField.Parent.ManualUpdate = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Function

Hi arvindkrkaushik,

Is the field with the dates that you want to filter in the Reports Filter area of the PivotTable or the Row Labels area?