Pivot Table Charts - Cum Count starts over every year

[mm9360a]

Basically I’m pulling in data from a DB for this chart. I need to create pivot table chart that has a cumulative sum of various status’ from year to year. The problem I’m running into is that it stops the cumulative sum after 2009 and starts over at 2010. I need it to keep cumulative sum from year to year. Does anyone have any suggestions?

 

[ShawnPCooke]

Hard to say without getting a bit more information on the structure. Is the Year in a separate field than the month, or quarter, or whatever your lower-level field is? If so, then there's your problem. You'd need to have all of the date information in a single field. It might be a good idea to create a separate column that can serve as a single time field.

For example, instead of having one column of data listing "2008", "2009", "2010" and another listing "May", "June", "July", etc., use a single column listed "May 2008", "June 2008", "July 2008", and so on.

 

[mm9360a]

Thanks for the reply! The date field is a long date, so basically a created on date of "5/13/2009 12:51:54 PM". They are not separated out. I pull those into the pivot table, then group by year/month. Is there anyway I can attach the worksheet here? That would really help.

 

[ShawnPCooke]

Hmmm... it may be the grouping that's interfering with your cumulative function. If you've got that long date field, you can use this formula to create a new column as I suggested:

=TEXT(A1, "mmmm yyyy")

(Where A1 is a cell in that long date format)

Copy that down, add it into your pivot table, and see if anything good comes of it.