+ Reply to Thread
Results 1 to 6 of 6

stacked column chart with data labels

  1. #1
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    stacked column chart with data labels

    hi all,

    My aim is to graph trends in amount of files & %age of missing POD's by station over weekly periods.*
    The concept I have (fixated!) in mind shown in the attached file with the weeks next to each other (& "POD missing" stacked under "POD Entered"*) but I can't wrap my head around how to actually create a chart like this concept.
    *with a "% Missing" as a data label for the latest week shown.

    I'm open to using PT charts or normal charts. Although a PT chart will save me investigating how to incorporate a "date slider" & "station picker" into any normal chart (PeltierTech.com will be my first stop!).

    my Questions:
    Can the Chart in the second screenshot (see attached file) be presented with no dates in the X axis & no space between each week for the specific stations?
    Is there a better way of presenting this information?

    btw, I've read a bit of Peltier/Chandroo/Few/Juice etc & am open to brutal comments about info overload/misdirection. However, I hope to nullify this by having the ability to select what is presented.


    Thanks
    Rob

    PS: I may not review this thread until the weekend.
    Attached Files Attached Files
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: stacked column chart with data labels

    Hi Rob,

    like this maybe? see attached.

    It's not the cleanest and with a little more time and study of this http://peltiertech.com/WordPress/clu...column-charts/ you may come up with a better solution.

    With the pivot chart approach you won't be able to get rid of the dates on the X axis, I think, but tricking with your original data table might be an idea.

    1. added a dummy value for each station with a date in 2020
    2. sorted the data table by station, then date ascending
    3. created a column for the x labels, where only the first occurrence of the label gets plotted
    4. set the x axis to show a tick every 3 categories
    5. set the data series to gap width 0, so now the dummy series provides the gap between the stations.

    Tell me if that's getting closer to what you need

    cheers

  3. #3
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: stacked column chart with data labels

    Thanks Teylyn,

    Yes, this seems to be heading in the right direction :-)

    I'll take some time to read through the link properly during the weekend* (& play with your upload). I got myself so tangled up in the "data labels" part when searching that I didn't include "stacked" in any of my searches & so I didn't run across this page on Jon's site.

    I might be thinking too big... but I'd like to be able have the data sorted so the chart is presented with the most "trackable files" on the left to allow for easier comparisons. I think this could be tricky as it seems to misplace the dummy series - hmmm, perhaps a dummy series that matches the size (of ?) & is made invisible to act as the gap.

    I'm hoping to get this finetuned to the point of a button push macro^ which will import the source data (columns B:G + column A) & then some "dynamic named ranges" will suss out the autoupdating of the chart on a nicely formatted page. With this goal in mind, my other concern is that I don't want to manipulate the raw data too much. However, I guess that once the required layout is sorted, VBA will make mincemeat of setting up the data.

    ^Ideally with someone else as the button pusher!

    *This pulverised my productivity in the office today & I should actually do something I can resolve tomorrow so I finish the week with some of the easier tasks completed.

    Thanks
    Rob

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: stacked column chart with data labels

    How about this.
    Added set of past and future records for each station so the columns can have the gap width removed but still have a gap between stations.
    Number format for Date field is "
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  5. #5
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: stacked column chart with data labels

    hi all,

    Sorry about the delayed feedback. I'm trying things out but am finding it quite slow to find Chart stuff in 2007 as I haven't done much chart work & am biased by what I know from 2003.

    Thanks Andy - Teylyn started me off nicely but I was hoping you'd pop by
    (saves me some time reading through all of Jon's pages - I still am reading them, but not as thoroughly)

    Yes Andy, I like the layout in that file.
    I'm now going to work on the placement of the labels (after a quick trip to buy Mrs B's Chrissy pressie!).
    For Chart readability I've created another column ("% Missing to Use") on the data sheet where I copy & paste the percent for the latest week. Currently the info in this column is manually updated but I will build it into an update macro. It's a copy & paste for the latest week because I couldn't get the chart to not show "0%" when I tried using a formula like
    Please Login or Register  to view this content.
    (which could be copied down).
    The macro approach should be sufficient (?^), but just out of curiosity,
    1) are there any formatting tricks (in the chart or PT) that would allow for a formula in all cells of the column?
    When trying to make the labels more readable, I used Rob Bovey's Chart Labeller to move them around on the chart, see the "Chart w last prcnt at base" sheet for what I thought may have been the best placement. Unfortunately, it seems this needs to be redone each time the PT is refreshed.
    2) Can the size of the axis be ajusted to make the labels more readable?
    3) Then I thought would it be possible to have a data table that only shows the %ages (not the other series)?

    4) How did you hide the dropdowns on the Pivot Chart & will it work in Excel 2003 (ie most computers at work*)?
    It's much tidier and I'll definitely use the technique for other projects. However, I think the dropdowns will be useful for this report as the data set grows & people may want to limit the chart to specific stations or time periods. ^Actually... dropdown flexibility may complicate the use of a simple macro for populating the new column but...

    *profile updated

    btw, as I look at the chart more, thoughts about "semantics"/NLP/"plan to succeed" are coming into play & I'm considering promoting positivity by changing the measure from "% missing" to "% Entered". Then managers/staff can be encouraged to work towards a higher/greater/better percentage (rather than using a negative measure that they try to make lower/decrease). At the risk of hijacking my own thread...
    5) Does anyone have experience or comments about the potential impact or worthiness of this sort of change in reporting measures?

    Thanks
    Rob
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: stacked column chart with data labels

    hi all,

    Hmmm, it's tricky going from a 2007 created PT back to 2003 - all sorts of options are disabled. (I've rebuilt a PT in 2003 & will upload it when possible)

    I've found answers to some of my questions:

    2 & 3)
    I feel these questions are now redundant because I've formatted the data labels (in their default locations) as Font - Superscript and they appear nicely for me (in my real file).

    4)
    Right click on a field button & select "hide PivotChart Field buttons", this raises a new question. Can the field buttons be made visible again with out using [ctrl + z] or
    Please Login or Register  to view this content.
    ?

    Rob

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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