+ Reply to Thread
Results 1 to 5 of 5

Macro to adjust pivot table's date filer for last week

  1. #1
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,179

    Macro to adjust pivot table's date filer for last week

    My only PT filter is a date filter. I need to set it to only include last week. M-F. Regardless if I run this on a tuesday or thursday, it should return the same results for the previous week. Can this be done? Also, I have my PTs to be Top 3 values, is there a way to make it stop at 3. What happens most commonly is I have 1,2,3,3,3. Is there a way to stop it at 1,2,3?
    Last edited by taylorsm; 10-23-2017 at 11:54 AM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,124

    Re: Macro to adjust pivot table's date filer for last week

    This sounds like a job for a helper column in the source data (or maybe two). One helper column will calculate whether the date is in the Mon-Fri range last week. Arrange this to evaluate to TRUE or FALSE and use it as the report filter. Another column could include rank. Do you have any criteria for breaking ties? If not and you willing to accept only the first occurrence then make a "composite key" that concatenates whatever fields are needed to make a record unique, and apply =Match(ConcatKey,ConcatCol,0)=ROW(). This evaluates to TRUE only for the first occurrence.

    This explanation would make a lot more sense with a concrete example.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,124

    Re: Macro to adjust pivot table's date filer for last week

    P.S. you don't need VBA to pull this off.

  4. #4
    Forum Contributor
    Join Date
    01-09-2016
    Location
    USA,USA
    MS-Off Ver
    2016
    Posts
    1,179

    Re: Macro to adjust pivot table's date filer for last week

    Here is an idea of what I am wanting. Sheet 1 has a lot more columns of data but they are not relevant for this pivot.

    Maybe it is easier to copy/paste the data and then remove it like I did manually but with a macro? I had help creating this macro that copy/paste values and retains formatting. Maybe I can adjust the macro to strip the excess info away?

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by taylorsm; 10-24-2017 at 04:49 PM.

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,124

    Re: Macro to adjust pivot table's date filer for last week

    OK try this:

    Add a couple of helper columns:
    Column C: =COUNTIFS([Error],[@Error],[Manager],[@Manager]) - this gets the number of times a combination occurs
    Column D: =COUNTIFS([Error],">"&[@Error],[Manager],[@Manager]) - this "alphabetizes by rank, the errors
    Column E: =[@Number]+[@Rank]/10 - this assigns a "composite based on number and rank

    The Pivot table takes the top 3 Places and sorts by them. These are the top 3 errors by manager with the tie breaker being the alphabetic position of the Error.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to get week-over-week % in pivot table for dissimilar data
    By adityag85 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-15-2016, 05:54 PM
  2. [SOLVED] Generic macro to adjust the source data for a pivot table
    By scottiex in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-10-2016, 11:41 PM
  3. Dynamic pivot filer
    By astolz in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-20-2014, 06:24 PM
  4. Pivot table field for day of week vs. Date return
    By MARKSTRO in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 01-19-2014, 06:18 PM
  5. Pivot table week grouping date format
    By meprad in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-04-2013, 06:37 AM
  6. [SOLVED] Macro to update pivot item (date format) in pivot table to latest date from data source.
    By rocksan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-10-2012, 03:18 AM
  7. Filer within Pivot Table
    By Rob in forum Excel General
    Replies: 2
    Last Post: 08-14-2005, 06:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1