PDA

View Full Version : Subtotal or pivot table using date fields



ibgreat
02-18-2009, 07:02 AM
I have spreadsheet that I would like to be able to be able to subtotal by week (ideally M-Su). Each row has data for a specific date. I took a look and was having trouble finding information on using dates with the subtotal function. This is the more immediate need, and I think easier solution.

However, I should be able to use a pivot chart and program the column to do this. I have done this in Access, but I am not seeing how to add custom fields to the pivot chart. Doing it in a pivot chart would be nice as I could program by week, review (4 weeks), or month.

Any thoughts and resources are greatly appreciated!

ibgreat
02-18-2009, 12:11 PM
Okay, I figured out how to use the WEEKNUM function to give me the week number that I can create a pivot table or chart around.

The data in the pivot table and pivot chart automatically uses Sum, this is perfect for most of what I want to do. But I also work with percentages, where we would want the calculation based on the underlying numerator/denominator. How can I play with the underlying function?

Also, is it possible to place multiple pivot charts onto a single worksheet?

ibgreat
02-18-2009, 12:51 PM
Actually, I realized the pivot table use count. I can manually change the setting to sum when I drop the field into the data area. Is there anyway to get the setting to stay and not keep defaulting to count?



Also, is it possible to place multiple pivot charts onto a single worksheet?


The perfect alternative to this would be to be able to stack all the fields onto one graph. So, ultimately instead of having all the fields listed on the x-axis as sub area of the main field they would be seperate graphs. Not sure if this makes sense. I can post an example if anyone needs a visual.

ibgreat
02-19-2009, 11:41 AM
In continuing to play with this, I was able to figure out how to display multiple pivot charts on one sheet. However, I have realized that I cannot manually change the chart properties as you can with typical charts. This is a significant issue.

I went back and have gotten used to using the subtotals on the worksheet. I can use this then manually create a chart based on that. What would work better is to be able to have the subtotal occur via VBA then change the data range for the charts(s) based on how the subtotal is calculated (e.g., week, month).

I am somewhat familiar with VBA in Access, but not accustomed to the properties within Excel. Any help and resources are appreciated.