Select latest week number only Pivot table

plexus · Sep 1, 2016

Hi Guys,

I hope you can help. I require the VB to be able to select the latest week only of a filter applied to a powerpivot table after its weekly refresh.

Whilst searching I came across the code below that performs the task based upon a date criteria. I was hoping it would not be too difficult a task to modify for latest week?

Code:

Sub MaxDatePivot()

   Dim pfiPivFldItem As PivotItem
   Dim dtmDate AsDate
   With Worksheets("Sheet1").PivotTables(1)
        .PivotCache.Refresh
        .ClearAllFilters
       With .RowRange
            dtmDate = Evaluate("MAX(IF(ISNUMBER(" & .Address(0, 0) & ")," & .Address(0, 0) & ",))")
       EndWith
       ForEach pfiPivFldItem In .PivotFields("End Date").PivotItems
            pfiPivFldItem.Visible = (CDate(pfiPivFldItem.Value) = CLng(dtmDate))
       Next pfiPivFldItem
   EndWith
   
EndSub

I hope someone can help me find a solution.

Thanks,

Plex.

Chihiro · Sep 1, 2016

It will largely depend on your PivotTable structure. Can you upload sample?

plexus · Sep 1, 2016

Chihiro said:
It will largely depend on your PivotTable structure. Can you upload sample?

Hi Chihiro,

Thanks for your support. Most appreciated.

I have loaded a test workbook on MS onedrivehere:

https://1drv.ms/f/s!AmV520RNN0VGfsRSWwcxUl7etRI

And also uploaded the file to the site.

Please let me know if require any more information.

Again, Thanks for your help!

Plex

plexus · Sep 1, 2016

Actually looking back again at this, a simple option would be to be able to apply a filter to the pivot table itself. The filter would need to be always for the latest week after a refresh. No need to select the data, just apply the filter after the refresh.

Chihiro · Sep 1, 2016

What type of calculation is used for your Week_Number?

Is it ISOWEEKNUM? Does week start on Mon or Sun? How do you treat beginning of the year?

For an example...
Jan 3, 2016
ISOWEEKNUM = 53
Week starting Sun: WEEKNUM(DATE,1) = 2
Week starting Mon: WEEKNUM(DATE,2) = 1

And you are always looking to filter Current WeekNum - 1? (I.E. for pervious week)

plexus · Sep 2, 2016

Chihiro said:
What type of calculation is used for your Week_Number?

Is it ISOWEEKNUM? Does week start on Mon or Sun? How do you treat beginning of the year?

For an example...
Jan 3, 2016
ISOWEEKNUM = 53
Week starting Sun: WEEKNUM(DATE,1) = 2
Week starting Mon: WEEKNUM(DATE,2) = 1

And you are always looking to filter Current WeekNum - 1? (I.E. for pervious week)

Hi Chihiro,

Its ISO week yes. I always want to select only the latest week. Would this be possible?

Chihiro · Sep 2, 2016

Will the report be limited to 1 year or will it contain different years? As that will impact coding logic.

Here's sample assuming that report contains info for single year only and filters for largest week number.

Code:

Sub Test()
Dim CurWkNum As Integer: CurWkNum = 1
Dim pvtI As PivotItem

With Worksheets("PIVOT").PivotTables(1)
    .PivotCache.Refresh
    .ClearAllFilters
    For Each pvtI In .PivotFields("WEEK_NUMBER").PivotItems
        If pvtI > CurWkNum Then
            CurWkNum = pvtI
        End If
    Next pvtI
    For Each pvtI In .PivotFields("WEEK_NUMBER").PivotItems
        pvtI.Visible = (CInt(pvtI.Name) = CurWkNum)
    Next pvtI
End With

End Sub

plexus · Sep 2, 2016

Chihiro said:
Will the report be limited to 1 year or will it contain different years? As that will impact coding logic.

Here's sample assuming that report contains info for single year only and filters for largest week number.

Code:

Sub Test() Dim CurWkNum As Integer: CurWkNum = 1 Dim pvtI As PivotItem With Worksheets("PIVOT").PivotTables(1) .PivotCache.Refresh .ClearAllFilters For Each pvtI In .PivotFields("WEEK_NUMBER").PivotItems If pvtI > CurWkNum Then CurWkNum = pvtI End If Next pvtI For Each pvtI In .PivotFields("WEEK_NUMBER").PivotItems pvtI.Visible = (CInt(pvtI.Name) = CurWkNum) Next pvtI End With End Sub

Wow that worked perfectly, thank you so much!. There will be no issue with the year as it will always only be the current years data.

I have 4 separate pivots on 4 separate worksheets, the last thing I need to ensure is that each pivot is updated to show the last weeks data. Would that just require duplicate code using the unique pivot names?

Thanks again ever so much.

Chihiro · Sep 2, 2016

If each sheet contains just 1 pivot each. I'd loop through each worksheet.
Assuming sheet containing pivot contains "Pivot" in the name.
Something like below.

Code:

Sub Test()
Dim CurWkNum As Integer
Dim pvtI As PivotItem
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If InStr(1, ws.Name, "Pivot") > 0 Then
        CurWkNum = 1
        With ws.PivotTables(1)
            .PivotCache.Refresh
            .ClearAllFilters
        For Each pvtI In .PivotFields("WEEK_NUMBER").PivotItems
            If pvtI > CurWkNum Then
                CurWkNum = pvtI
            End If
        Next pvtI
        For Each pvtI In .PivotFields("WEEK_NUMBER").PivotItems
            pvtI.Visible = (CInt(pvtI.Name) = CurWkNum)
        Next pvtI
        End With
    End If
Next ws
End Sub

Edit: Note the change of location for "CurWkNum = 1" line. This is needed to reset CurWkNum for each Pivot.

plexus · Sep 2, 2016

Chihiro said:

If each sheet contains just 1 pivot each. I'd loop through each worksheet.
Assuming sheet containing pivot contains "Pivot" in the name.
Something like below.

Code:

Sub Test()
Dim CurWkNum As Integer
Dim pvtI As PivotItem
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If InStr(1, ws.Name, "Pivot") > 0 Then
        CurWkNum = 1
        With ws.PivotTables(1)
            .PivotCache.Refresh
            .ClearAllFilters
        For Each pvtI In .PivotFields("WEEK_NUMBER").PivotItems
            If pvtI > CurWkNum Then
                CurWkNum = pvtI
            End If
        Next pvtI
        For Each pvtI In .PivotFields("WEEK_NUMBER").PivotItems
            pvtI.Visible = (CInt(pvtI.Name) = CurWkNum)
        Next pvtI
        End With
    End If
Next ws
End Sub

There are other tabs that contain pivots that I do not want to filter. Would this solution loop through the whole workbook?

Chihiro · Sep 2, 2016

It loops through all worksheet that contains "Pivot" in sheet name.

Alternately you can construct array of Worksheet names that you want to check. Something like below.

Code:

Sub Test()
Dim CurWkNum As Integer
Dim pvtI As PivotItem
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets(Array("PIVOT", "PIVOT (2)", "PIVOT (3)", "PIVOT (4)"))
        CurWkNum = 1
        With ws.PivotTables(1)
            .PivotCache.Refresh
            .ClearAllFilters
        For Each pvtI In .PivotFields("WEEK_NUMBER").PivotItems
            If pvtI > CurWkNum Then
                CurWkNum = pvtI
            End If
        Next pvtI
        For Each pvtI In .PivotFields("WEEK_NUMBER").PivotItems
            pvtI.Visible = (CInt(pvtI.Name) = CurWkNum)
        Next pvtI
        End With
Next ws
End Sub

Replace "PIVOT", "PIVOT (2)"... with your actual worksheet names.

plexus · Sep 2, 2016

Chihiro said:

It loops through all worksheet that contains "Pivot" in sheet name.

Alternately you can construct array of Worksheet names that you want to check. Something like below.

Code:

Sub Test()
Dim CurWkNum As Integer: CurWkNum = 1
Dim pvtI As PivotItem
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets(Array("PIVOT", "PIVOT (2)", "PIVOT (3)", "PIVOT (4)"))
        CurWkNum = 1
        With ws.PivotTables(1)
            .PivotCache.Refresh
            .ClearAllFilters
        For Each pvtI In .PivotFields("WEEK_NUMBER").PivotItems
            If pvtI > CurWkNum Then
                CurWkNum = pvtI
            End If
        Next pvtI
        For Each pvtI In .PivotFields("WEEK_NUMBER").PivotItems
            pvtI.Visible = (CInt(pvtI.Name) = CurWkNum)
        Next pvtI
        End With
Next ws
End Sub

Replace "PIVOT", "PIVOT (2)"... with your actual worksheet names.

Thanks so much for your continued support.

I understand that I need to change the array names. I have tested the script but kept getting a 'runtime error 9' 'subscript out of range' error. I have attached the spreadsheet as I am sure I am just being dumb.

Chihiro · Sep 2, 2016

Change your array of sheet names to below.

Code:

Array("PIVOT", "PIVOT 2", "PIVOT 3", "PIVOT 4")

I.E. No brackets around numbers. I had it since I just copied "PIVOT" sheet 3 times and didn't rename them.

plexus · Sep 2, 2016

Chihiro said:
Change your array of sheet names to below.

Code:

Array("PIVOT", "PIVOT 2", "PIVOT 3", "PIVOT 4")

I.E. No brackets around numbers. I had it since I just copied "PIVOT" sheet 3 times and didn't rename them.

Yeah I changed that and I still get the same error. I did a few naming tests and got the same error as above on all.

Chihiro · Sep 2, 2016

Hmm? See attached. Worked fine on my end.

plexus · Sep 2, 2016

Chihiro said:
Hmm? See attached. Worked fine on my end.

I had renamed the pivots and the VB did not match the pivot names due to removal of space ie Pivot1

Thank you ever so much for your help and having the patience of a saint with a VBA dummy like me.

So grateful!

plexus · Sep 6, 2016

Chihiro said:
Hmm? See attached. Worked fine on my end.

Hi Chihiro,

I am having an issue running this on the live workbook. The script hangs and when I esc out the debugger shows me an error on the line:

.PivotCache.Refresh

The pivot I am working with has a lot of data. Could this error be caused due to the amount of data in the refresh. If that is the case is it necessary to refresh the pivot cash?

Thanks,

Plex.

Chihiro · Sep 6, 2016

What is the error message that comes up?

I haven't had issue with data size for Pivots. Both with data model and table as data source (ex. data model I deal with around 900k rows of data, table around 400k rows). But then I have 16Gb of RAM.

plexus · Sep 8, 2016

Chihiro said:
What is the error message that comes up?

I haven't had issue with data size for Pivots. Both with data model and table as data source (ex. data model I deal with around 900k rows of data, table around 400k rows). But then I have 16Gb of RAM.

Hi Chihiro,

I run a very large amount of data as I use the pivots from PowerPivot tables. I ran it from my desktop with 32GB RAM and it just about manages it (after a few mins ). My Laptop also had 16GB and it was just too much on the virtual memory.

Thanks again!

Chihiro · Sep 8, 2016

Hmm, couple of suggestions then.

1. If your copy of Excel is 32 bit, install 64 bit
2. Do most of data transformation at Query/Stored Procedure stage on Server side

You really shouldn't be dealing with more than 2GB of data. For which 16GB/32GB Ram is plenty.

plexus · Sep 22, 2016

Chihiro said:
It loops through all worksheet that contains "Pivot" in sheet name.

Alternately you can construct array of Worksheet names that you want to check. Something like below.

Code:

Sub Test() Dim CurWkNum As Integer Dim pvtI As PivotItem Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets(Array("PIVOT", "PIVOT (2)", "PIVOT (3)", "PIVOT (4)")) CurWkNum = 1 With ws.PivotTables(1) .PivotCache.Refresh .ClearAllFilters For Each pvtI In .PivotFields("WEEK_NUMBER").PivotItems If pvtI > CurWkNum Then CurWkNum = pvtI End If Next pvtI For Each pvtI In .PivotFields("WEEK_NUMBER").PivotItems pvtI.Visible = (CInt(pvtI.Name) = CurWkNum) Next pvtI End With Next ws End Sub

Replace "PIVOT", "PIVOT (2)"... with your actual worksheet names.

Hi Again Chihiro,

I have another question. I am now working on a forecasting spreadsheet that contains date ISO current week plus data 10 weeks in the future.

How can I modify the VBA to select the 4 next weeks data?

As we are currently on week 38 I would want to select week 39,40,41 and 42.
I would always want to select the 'next 4 weeks'.

Hope you can help,

Thanks,

Plex.

plexus · Sep 26, 2016

Any good Samaritans out there? I am hopeless with VBA and have searched the internet for this and asked on several forums. I was hoping this would be a simple edit. I managed to work around some of my original requirements and now only require the macro to select as above plus the next 4 weeks data. So I guess something like:

Code:

If pvtI > CurWkNum Then
CurWkNum = pvtI +1CurWkNum = pvtI +2CurWkNum = pvtI +3CurWkNum = pvtI +4

Chihiro · Sep 26, 2016

That would change code quite a bit. As you will no longer be looking for MaxWeekNum (i.e. first inner loop operation in the code).

You will need to define current week by code or some other means.

Using code to define current week it would be something like below.

Code:

Sub Test()
Dim ISOweekNum As Integer
Dim pvtI As PivotItem
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets(Array("PIVOT", "PIVOT 2", "PIVOT 3", "PIVOT 4"))
        With ws.PivotTables(1)
            .PivotCache.Refresh
            .ClearAllFilters
        ISOweekNum = DatePart("ww", Date, vbMonday, vbFirstFourDays)

        For Each pvtI In .PivotFields("WEEK_NUMBER").PivotItems
            pvtI.Visible = (CInt(pvtI.Name) >= ISOweekNum And CInt(pvtI.Name) <= ISOweekNum + 4)
        Next pvtI
        End With
Next ws
End Sub

Edit: If current week number (ISOweekNum) is to be excluded. Just change ">=" to ">".

Select latest week number only Pivot table

New Member

Excel Ninja

New Member

Attachments

New Member

Excel Ninja

New Member

Excel Ninja

New Member

Excel Ninja

New Member

Excel Ninja

New Member

Attachments

Excel Ninja

New Member

Excel Ninja

Attachments

New Member

New Member

Excel Ninja

New Member

Excel Ninja

New Member

New Member

Excel Ninja