+ Reply to Thread
Results 1 to 10 of 10

Multiple Pivots, One Filter, etc.

  1. #1
    Registered User
    Join Date
    10-16-2009
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2007
    Posts
    6

    Multiple Pivots, One Filter, etc.

    This is a shot in the dark, but I am looking for a way to combine three pivot tables in to one. Basically, one sales rep will have data for the current month, the year to date, and the total year. I have to compile this data to one sheet, for 30 sales managers! The problem with running the pivot three times (once for monthly, year to date, and yearly) is that brands that aren't sold in the month are then left off the table, which leads to a mess that it takes far too long to clean up. I hope I've made this clear, but please check the attachments for a better idea of what I'm looking to do...thanks.
    Attached Files Attached Files

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Multiple Pivots, One Filter, etc.

    I fear you won't get much of a response with images - if you need to demo your setup then post a small sample file that mimics your setup and outline clearly your requirements.

  3. #3
    Registered User
    Join Date
    10-16-2009
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Multiple Pivots, One Filter, etc.

    Thanks for the tip. So, here it is again. In Excel.

    I am really hoping to show 3 different time periods (Month, Year to Date, and Total Year) next to each other, and with all Customer and Brand possibilities visible on all three periods. Thanks.
    Attached Files Attached Files

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Multiple Pivots, One Filter, etc.

    Attached is one approach (2007 format), ie adding some calcs @ source.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-16-2009
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Multiple Pivots, One Filter, etc.

    Very cool. Let me work through this and see if I have any questions. Thanks Donkey.

  6. #6
    Registered User
    Join Date
    10-16-2009
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Multiple Pivots, One Filter, etc.

    Donkey, using the approach above, can I set up 12 columns, each one adding all figures from each fiscal month? My problem with the solution above is that if I try to report on the month end October numbers, and it's now November 2, my "Current" field above will not be correct, right? Thanks again.

    I don't necessarily need the 12 columns for each month...Again, I'm deferring to you. Thanks.

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Multiple Pivots, One Filter, etc.

    Well to be honest, if you wish to be able to manually control which month to be the "current" period then rather than using TODAY() in your calcs simply refer to a cell containing the month period of interest.

    Using the earlier sample as example:

    Please Login or Register  to view this content.
    This approach has the added bonus of making all of your calcs non-volatile (TODAY() being a Volatile Function).

    ...and if you really wanted you could use VBA to get the PT to auto refresh whenever you alter D1 on the PT sheet

    EDIT: not sure when you or I will next be online but assuming...

    a) you choose to adopt the above (ie D1 holding month period of interest)

    b) were interested in auto refreshing the PT as and when the month period was altered

    then roughly speaking the below is what you're looking at...

    Please Login or Register  to view this content.
    to insert the above into the earlier sample file, right click on Sheet1 and select View Code, paste the above into the resulting window.
    Thereafter you need to ensure the file is saved in a compatible format (eg .xlsm as opposed to .xlsx) and you need to ensure going forward Macros are enabled when opening the file (else you will need to refresh the old manual way!)
    Last edited by DonkeyOte; 11-05-2009 at 02:59 PM.

  8. #8
    Registered User
    Join Date
    10-16-2009
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Multiple Pivots, One Filter, etc.

    The VBA might be a little much for me just yet, but I'll definitely give the other piece a shot. Thanks.

  9. #9
    Registered User
    Join Date
    10-16-2009
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Multiple Pivots, One Filter, etc.

    So the formula

    =N2*(K2=10)

    where N2 is the money and K2 is the fiscal month. I've set it as 10 for October above since that's the month I'm reporting on.

    Such a simple formula, but I've never learned the power of the *.

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Multiple Pivots, One Filter, etc.

    Though as shown earlier I would suggest storing the month of interest in one separate cell (ie entering 10 into a cell of choice) and then having the remaining formulae reference said cell rather than hard wiring the month of interest into the formula... going forward this means you need only change the one cell ... all calculations will update simultaneously in accordance with the change.

    In the case of N2*(K2=10) ... K2=10 will return a Boolean of TRUE/FALSE ... Booleans when coerced become 1/0 respectively... hence N2*TRUE equates to N2*1 (ie the act of * coerces the Boolean to Integer equiv.) and N2*FALSE equates to N2*0.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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