• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Date Filter

Neeraj Dhiman

New Member
Hi All,

Need to understand the problem with below code. I am trying to filter the data between two dates however the below codes is working but its not selecting all the dates between the defined dates set. Please help me here

Start date is : 1 Jan 2019
End Date is : 31 mar2019

Code:-
Code:
Sub PageItemFilter()
Dim pvtF As PivotField
Dim pvtI As PivotItem

Set pvtF = Worksheets("PivotTables").PivotTables("PivotTable1").PivotFields("Date")
For Each pvtI In pvtF.PivotItems
    If DateValue(pvtI.Name) >= Range("C2").Value2 And DateValue(pvtI.Name) <= Range("C3").Value2 Then
        pvtI.Visible = True
    Else
        pvtI.Visible = False
    End If
Next pvtI

End Sub
 
Last edited by a moderator:
Neeraj Dhiman
Your thread let understand that You would like to use Filter
... but for some reason You compare with If ... and ...
Have You tried to ... use Filter with Your Pivot-table?
 
Why use code to filter? It's far easier to control Pivot Tables through slicers ;)

If code is required andiIf you have Excel 2010 or later, I'd recommend using OLAP based Pivot Tables to take advantage of .VisibleItemsList = Array(Value1, Value2...). To set all visible items at once.

This will be much faster than iterating over each individual pivot item to set it's property.
 
I am using MS office 2016 not sure how to use OLAP based pivot. Can you help me with OLAP based pivots
 
Last edited by a moderator:
I have a file wherein i have data for more then two year however when i create pivot i need the data only for current quarter but using this code it will select some unexpected dates as well as its not selecting all date between the range. I have attached a temp sheet for your reference
 

Attachments

  • Broker Test.xlsm
    47.5 KB · Views: 4
Last edited by a moderator:
Neeraj Dhiman
Many things are more clear with a sample-file.
Basic
... You missed few lines in Your code
... You need set only False's.
Check this sample ...
 

Attachments

  • Broker Test.xlsm
    49.7 KB · Views: 7
Thanks for updated code however i am still getting error on:-
'If DateValue(pvtI.Name) < Range("C2").Value2 Or DateValue(pvtI.Name) > Range("C3").Value2 Then pvtI.Visible = False" (Error "1004" Enable to set the visible property of the pivot item class )
 

Attachments

  • Error 1004.PNG
    Error 1004.PNG
    21.9 KB · Views: 10
  • Error.PNG
    Error.PNG
    14.3 KB · Views: 10
Last edited by a moderator:
... did You changed date?
... or did You something else?

I didn't get any error ...
... and without Your file, it's a challenge to guess!
 
Last edited:
No, I did not made any changes in file however it taking date for June month as well in filter field please check the attached file
 

Attachments

  • Broker Test (1).xlsm
    48.1 KB · Views: 5
Last edited by a moderator:
Neeraj Dhiman
Seems that You are using date format as m/dd/yy ... I don't use?
Screenshot 2019-09-18 at 12.04.24.png
Screenshot 2019-09-18 at 12.06.47.png
Check, how do You write those dates above.
This way ( d/m/yyyy ) it works in many places.
 
Since, you have Excel 2016. Here's what I would do.

Select your data range. Go to Insert -> Pivot Table.

In the dialog window, check "Add this data to the Data Model".

Now make your pivot table as normal. But don't add "Date" column to Filters field (page field).

Page field is special in that it treats all values as categorical (i.e. non-sequential) and lacks several properties that makes it easy to work with Pivot Fields.

Instead, either add it to Row label under "Status" or as Slicer.

Code for slicer would be...
Code:
Sub Demo()
Dim slc As SlicerCache
Dim sli As SlicerItem
Dim iDic As Object
    Set slc = ThisWorkbook.SlicerCaches("Slicer_Date")
    Set iDic = CreateObject("Scripting.Dictionary")
   
    For Each sli In slc.SlicerCacheLevels(1).SlicerItems
        With Sheets("Sheet1")
            If CDate(sli.Value) >= .Range("C2").Value2 And CDate(sli.Value) <= .Range("C3").Value2 Then
                iDic(sli.Name) = 1
            End If
        End With
    Next
   
    If iDic.Count = 0 Then
        MsgBox "There is no data in date range specified"
        Exit Sub
    Else
        slc.VisibleSlicerItemsList = iDic.Keys
    End If
End Sub

See attached.
 

Attachments

  • Broker Test (1).xlsm
    364.4 KB · Views: 26
Back
Top