Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2012
    Posts
    13

    Pivot Table-Running Total (two years)

    I'm putting together a report that shows sales versus defects. I have a 12 month rolling, running total for sales numbers, and defect numbers. The problem is that I am using data from Aug-2011 thru Sept-2012. My running total will only add the months together that are in the same year.

    I cannot figure out how to add more than one year. I have attached an image to show where I am at in the process. Any help is appreciated.
    Attached Thumbnails Attached Thumbnails pivot.JPG  

  2. #2
    Join Date
    Jan 2009
    Location
    Louisiana, United States
    Posts
    163
    Let me make sure I understand this. At the end of the month, you add one row for that month with sales numbers and defect numbers. What you want is to show the total for the past 12 net months for both sales and defects?

    So say you just added October numbers to your spreadsheet. You would want your pivot table to show total sales and defects for the ranges:
    Nov 2011 - Oct 2012
    Nov 2010 - Oct 2011
    Nov 2009 - Oct 2010

    Then in the next month, you would want these ranges in your pivot table to change by one month so that they are still net 12 month summaries?

  3. #3
    Join Date
    Jul 2012
    Posts
    13
    Yeah, I needed it to add together October 2012's units back through November 2011. And then the next month would Add November 2012, and drop off Nov 2011 and so on. I've just created a report that I do a VLOOKUP off of every month.

  4. #4
    Join Date
    Jan 2009
    Location
    Louisiana, United States
    Posts
    163
    I'm attaching a spreadsheet showing how to calculate net 12 months without vlookup. At the end of the month, you just woudl have to add the date, sales and defects and drag down the calculated fields.

    running totals/net 12 month totals are tricky to calculate. There might be a better way to do it but this is how I would do it.

    I'm not a huge pivot table fan because pivot tables resize to match your data set which can throw off referrences, I prefer working with sumifs, countifs and vlookup to create something similar and then just check the balance against the balance being looked up to make sure nothing is being missed.

    Hope this helps!

    Joshua
    Attached Files Attached Files

  5. #5
    Join Date
    Jul 2012
    Posts
    13
    Yeah that was pretty neat. Thanks for the help

Tags for this Thread

Posting Permissions

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