If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > Pivot Table-Running Total (two years)

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Pivot Table-Running Total (two years)-pivot.jpg  
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
File Type: zip example.zip (7.4 KB, 15 views)
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jul 2012
Posts: 13
Yeah that was pretty neat. Thanks for the help
Reply With Quote
Reply

Tags
12 month rolling, pivot, running total

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On