Help with stacked bar chart showing annual call activity?

[ms30594]

Working on a project to show text and voice call activity between sales reps and customers. Here is a small sample of what the data looks like:

The goal is to display call activity for each day of the year to show trends and volume of activity. The horizontal axis will be day of the year and show an empty space if there is no data. For days with data, the options are: text message in, text message out, call in, call out, pict video MSG in, pict video MSG out.

Each day will show the total number of actions. For the 12/09/11 example above, the total number of actions is 4, 3 text messages out, 1 in. The stacked bar graph for that day will show 3 messages out in one color and 1 message in in another color. For other action types, same rules apply, just a different color.

I have been beating myself up on this and just haven't been able to nail it. My guess is that it requires some formulas or scripting to count each day's activity and use that value versus simply selecting it using the menu-driven features. I am thinking that 365 bars across the page may be excessive so I may break it down by quarters but all of the rules above apply.

Thanks in advance for the suggestions/recommendations/solution.

 

[VBA Geek]

With a pivot table you can easily group your data by Quarters and create quick pivot charts:

Download the file

With a bit more effort you can include the totals:

If you want to show the monthly trends then you can group by months and create a line chart. The one shown below is a panel chart showing all 3 types divided by In and Out in 1 chart but you could use again a pivot table and make 3 individual charts per each type:

 

[Toonses]

Hi, I created a custom combination: clustered & stacked column chart from the data; however the positive data labels (green sections) are all reading zeros and not the values. The negative variances (red) are reading correctly. Here is a copy of the date table.

	Month	Actual Total	Goal	Base Var.	Pos Var.	Neg Var.	Variance
	Jul-17	50	100	50		50	-50
	Aug-17	120	150	120		30	-30
	Sep-17	200	175	175	25		25
	Oct-17	210	200	200	10		10
	Nov-17	220	250	220		30	-30
	Dec-17	44	35	35	9		9
	Jan-18	47	40	40	7		7
	Feb-18	54	50	50	4		4
	Mar-18	59	63	59		4	-4
	Apr-18	63	70	63		7	-7
	May-18	68	75	68		7	-7
	Jun-18	75	150	75		75	-75

<colgroup><col><col><col><col span="5"></colgroup><tbody>
</tbody>

Please help! It will not allow me to paste or screen shot a pic of the chart.