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

 

[GlennUK]

That's easy ... from within the pivottable just create a chart ... it will be connected to the pivottable. Well, that's the theory anyway ... can you try it and see what happens?

 

[StrawS]

Hey Glenn,

That seems to have worked well but there's a few quirks.

firstly: FYI - I couldn't group fields when uncompleted tasks had a "0" in the "date completed" column. I had to use "1" instead.

secondly: my data starts from december 2010 and goes through to June 2011. With the settings you've described, the chart ends up plotting Jan 2010 to Dec 2011. How can I get it to start from December and go to the current month (aka June)?

It looks like we're on the right track with your solution. Just need to tweak those dates somehow.

Thanks
Straws

 

[StrawS]

I have another tweak I need to make in regards to the legend.

I have too many colours on the graph, and it will only get worse as I complete more projects. Further, when printed in Black & White, the legend won't work at all.

The solution I can see would be to replace colours with letters. For example, each block would have a letter in the middle, and correspondingly - the legend would have letters instead of little colour squares.

These letters could be randomly assigned and would not need to match the project letters. Actually, my real data doesn't have project letters, it has names instead but I replaced them with letters in my post for annonymity. So it wouldn't be confusing at all if the legend letters were randomly assigned.

Regards

 

[GlennUK]

To stop empty months showing, change the field setting and untick the "Show Items With No Data" checkbox.

Randomly have letters as the project, as you created for annonymity, so that they are the series names.



In the PivotChart add labels to each series, as series name. If you don't want to do that by hand, a macro can do that for you, similar to this:

    For i = 1 To ActiveChart.SeriesCollection.Count
        
        ActiveChart.SeriesCollection(i).ApplyDataLabels
        ActiveChart.SeriesCollection(i).DataLabels.ShowValue = False
        ActiveChart.SeriesCollection(i).DataLabels.ShowSeriesName = True
        
    Next

 

[GlennUK]

You might want to have the background of the labels as white, so include this line in the code:

ActiveChart.SeriesCollection(i).DataLabels.Interior.ColorIndex = 2

 

[StrawS]

Hi there,

Thanks for your replies!!

I've managed to implement the "labels as series name" to get the letters showing instead of the number of work units.
However, I'm amazed it takes a macro to edit all of the blocks ("series") at the same time.
Especially since you initially apply "number of work unit" values to all blocks at the same time when you turn on the Data Labels in the first place (by clicking PivotChart Tools -> Layout -> Labels -> Data Labels -> Center).

Do you (or anyone else) know of a way to edit all series at once? Perhaps by altering the which data the "Data Labels" uses by default before switching them on?



Regarding the other issue - showing months before the data starts, you responded:

To stop empty months showing, change the field setting and untick the "Show Items With No Data" checkbox.

The problem with this is that it will also stop showing the months with no data that come after the first month with data.
In other words, your solution gets rid of the empty months in 2010 (Jan-Nov), but it also gets rid of January and March 2011.
This breaks the continuity of the resulting graph.

How do we get rid of the Jan-Nov 2010 months while leaving the rest visible?

 

[GlennUK]

Can you select just those to be hidden in the drop down for the date? I haven't got a test file to try this on at the moment.

 

[StrawS]

No, if I filter out the month of January - November in 2010, that filter applies to all years, so the 2011 data doesn't show any more.

 

[GlennUK]

Ah, I see ... yeah, I seem to remember that's how it works. I'll try to set up some test data today, and see what can be done to limit the grouping to be the date range that you want.

 

[GlennUK]

It looks like you'll have to have extra values for Jan and Mar 2011, zeroes say, for Project A, for those months to appear in the pivottable, if you want to have those missing months to appear. Not a very good solution I'm afraid. I'll carry on experimenting during my spare time, but don't hold your breath for a better solution.