Stacked Column chart that groups dates by month

[StrawS]

Gidday,

I have the following list of data and am having trouble arranging it in a stacked column chart.
Column A = Completed date
Column B = Project name (just a letter)
Column C = Work units (the variable data).

--------------------------
Completed Project Work units
15/12/2010 A 30
20/12/2010 B 67
23/12/2010 C 92
14/02/2011 D 108
16/02/2011 E 50
18/02/2011 F 45
18/02/2011 G 29
4/04/2011 H 38
6/04/2011 I 8
6/05/2011 J 256
K 444
22/06/2011 L 242
M 21
N 16
O 29
P 146
Q 175
R 191
------------------------------

Chart specifications:
Y axis = "Work units" data.
X axis = time measured in months (one column per month).
Layers of each column = the various projects completed in that month.

Chart should not plot records with no "completed" date.
Chart should display months where no projects were completed.

Please let me know if the solution requires my data to be sorted in any particular way (I would ideally like a solution which doesn't require any sorting, but am open to anything).

So far I've been fluffing round with pivottables to try and arrange the data before charting... but I suck at pivottables so am getting nowhere. Not even sure if that's a necessary step.

Thanks for any help!

Straws

 

[Steve=True]

This link shows you the structure you will need to build for your data.

 

[Steve=True]

This links shows you the structure for your data and your data sample graphed.

http://www.4shared.com/document/Dcg8HHKo/stacked_Project_bar_by_month.html

 

[StrawS]

Thanks Steve, but your solution does not fulfil the requirements.

The graph produced does not display months where no projects were completed.
The graph is plotting projects which have not been completed yet.
Also, the data has to be rearranged, which is not ideal.

Thanks for your time but I'm still looking for a solution here.

 

[GlennUK]

I'd use a PivotTable and PivotChart, but you need to fill in your uncompleted dates with zeroes, so that you can group the date field.

Create a pivottable, with Completed in the Row label area, and Project as the Column labels, and Work_unitsin the Values data area.

Right-click a date in the pivottable, and choose Group from the shortcut menu, and choose Month and Year to group by, and enter a suitable Ending at: date, say 31/12/2011.

Then right-click any month, and choose Field Settings, and tick the Show Items with No Data checkbox ( in the Layout and Print tab ).

Don't panic when you see all the years from 1900 onwards ... filter the year, and click on Select All to deselect all, then scroll to select only 2010 and 2011.

Create your pivotchart.

 

[Steve=True]

Sorry straws, didn't see those requirements.

You can simply insert cells from column F to Y for the months that are not showing and add them.

Also, i don't see that any non-completed projects have been plotted, for instance, M is not plotted, etc.

Finally, eventhough the data has to be rearranged, this can be done with formulas that look up the values, like index/match.

Good luck.

 

[StrawS]

Hi,

I'm understand about the rearranging of the data via formulas and inserting cells to represent the months where no projects were finished. But all of this is very manual and I figure there must be a better solution which involves tampering with settings in the chart, or which would use a pivottable as part of the solution (also a vehicle for rearranging the data, but one that would be a more streamlined solution).

There are #N/A values plotted in your graph on the June column. I believe these are coming from the cells representing the projects which are not finished (no completion date).

There are also strange black squares at various points on the columns. I'm not sure where they're coming from.

In any case, thanks again for your help but I think a different solution is needed... if it exists.

Anyone up for the challenge??

 

[StrawS]

Still looking for a solution here. Can anyone help?

 

[GlennUK]

I am understanding that you have not tried my solution?

 

[StrawS]

Hey GlennUK,

No I didn't see your post there. Completely overlooked it sorry.

Well, that's exactly the kind of pivottable I wanted to come up with, so thank you for that. It's perfect.

I'm still not sure how to churn out a Pivotchart with it since I'm really just a newbie with the pivottable/chart side of excel.

Can you give me some pointers?

thanks

Straws