+ Reply to Thread
Results 1 to 4 of 4

Help with stacked bar chart showing annual call activity

  1. #1
    Registered User
    Join Date
    06-16-2018
    Location
    U.S.A.
    MS-Off Ver
    Excel Mac 2011
    Posts
    2

    Help with stacked bar chart showing annual call activity

    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:

    Screen Shot 2018-06-15 at 8.45.14 PM.png

    *UPDATE* 6/16/18 File uploaded to post

    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 90 days per page but all of the rules above apply.

    Thanks in advance for the suggestions/recommendations/solution.
    Attached Files Attached Files
    Last edited by ms30594; 06-16-2018 at 09:31 PM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help with stacked bar chart showing annual call activity

    Hi and welcome to the forum

    A Pivot Table with its assciated Pivot Chart is probably the answer, but upload the workbook rather than a picture, they're usually no help since non of us wish to recreate data that you already have.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    06-16-2018
    Location
    U.S.A.
    MS-Off Ver
    Excel Mac 2011
    Posts
    2

    Re: Help with stacked bar chart showing annual call activity

    Thank you for the reply Richard. I uploaded a copy of the file to the original post. Thank you in advance for any insight.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help with stacked bar chart showing annual call activity

    See the attached.

    Your dates in column A are not proper date numbers, just strings. So I've converted the 2011 dates to proper date numbers in a new column I.
    The pivot table and associated chart only use the data you supply. If you want to show the favct that there is no data for some days then I'd be inclined to create a new separate table that contains all the 365/366 days in a year and alongside use a COUNTIF() function to record the number of items per day. Then use that new table as the source for your pivot table and chart.

    Incidentally if you use the Pivot Table then I'd be inclined to have every year's data in a single table and use a Pivot Table Slicer to filter the PT by year.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 04-22-2016, 05:25 PM
  2. Replies: 0
    Last Post: 01-07-2015, 01:22 PM
  3. Replies: 1
    Last Post: 10-10-2014, 09:53 AM
  4. [SOLVED] Column chart showing activity over 24 hour period
    By zoobaby in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 03-16-2013, 07:48 AM
  5. My stacked column Bar chart is not showing the right data!
    By JamesR12 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-14-2013, 07:11 AM
  6. Stacked Column Chart showing the Limits
    By rose4emi in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 11-12-2012, 04:15 AM
  7. Replies: 0
    Last Post: 03-02-2012, 12:54 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1