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.
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?
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.
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.