18 Replies Latest reply: Jan 4, 2011 4:45 AM by Enrique Herranz RSS

    Stacked Bars - How to display one thinner bar inside another (wider) bar ?

    Enrique Herranz

      I need to use a Bar chart to display project actual hours versus project budgeted hours. So I have one dimension ( project tasks ) and two expressions ( actual hrs and budgeted hours ). Ideally I would need to have one bar ( actual hours ) inside the other bar ( budgeted hours ), both starting at 0.

      This would help me to save a lot of space in the chart ( compared to using two bars side by side for every task ), and also task progress could be monitored easily , and hours overruns would quickly catch my attention ( the inside thinner bar would cross the outside bar )

      I tried setting up a stacked bar chart and playing with the Bar Offsets of every expression, but I can´t make it work .

      Does anybody know if this type of chart is possible in Qlikview ? Also, how is it possible to set up the bars width, so one ( the one inside ) is thinner than the second bar ?. Please see attached image of such display I got from another data visualization tool ( Tableau ).

      I would appreciate any help on this.

      Thank you

      Enrique

       

        • Stacked Bars - How to display one thinner bar inside another (wider) bar ?
          Enrique Herranz

          Any advice from someone please ? I need to know if this is possible in QlikView somehow ...or just to forget about that display for the bar charts.

          Thanks a lot for whatever help / advice you can give me

          Enrique

            • Stacked Bars - How to display one thinner bar inside another (wider) bar ?
              John Witherspoon

              I don't think you can do that directly. I suspect that in QV10 you could do it as an extension, but I have no idea how extensions work (I'm still on version 9). It wouldn't surprise me if someone's already written a bullet graph extension that you could use, though this is a little simpler than a bullet graph.

                • Stacked Bars - How to display one thinner bar inside another (wider) bar ?
                  Enrique Herranz

                  Thanks for your reply John. I have no experience either with version 10 yet, I simply downloaded it and briefly had a look at the new features and I also saw that Gantt Charts are now possible using the extension.

                  Does anybody have experience with QV10 extensions setting up bullet graphs ? Any instructions you could share here ?

                  Thanks a lot

                    • Stacked Bars - How to display one thinner bar inside another (wider) bar ?
                      Daniel Rozental

                      I have messed around a bit with QlikView extensions, it's not too hard to do although there isn't much documentation about it.

                      I haven't done or seen a bullet graph extension.

                      Can't you just do a dot inside the bar? not really as nice but might be acceptable.

                        • Stacked Bars - How to display one thinner bar inside another (wider) bar ?
                          Enrique Herranz

                          Daniel, how can I place a dot inside the bar ? Is this done just using a combo chart setting bars for the budgeted hrs and a line ( only displaying the dots ) for the actual hours ? or do I need to overlay two different charts ?

                          Thanks for the trick ...as you say it´s not so nice but could do the work.

                          I really would appreciate some feedback from someone who already setup bullet graphs in QlikView. In fact I don´t understand how that type of chart is not available already as a subtype within the bar charts. I am not a relative of Stephen Few, but I think the bullet graph is a very interesting space efficient chart.

                            • Stacked Bars - How to display one thinner bar inside another (wider) bar ?
                              Daniel Rozental

                              You can do that with a combo chart just as you said.

                              Or you could use a sparkline, take a look at the Data Visualization sample, Sparklines tab.

                              • Stacked Bars - How to display one thinner bar inside another (wider) bar ?
                                John Witherspoon

                                 


                                Enrique wrote:I really would appreciate some feedback from someone who already setup bullet graphs in QlikView. In fact I don´t understand how that type of chart is not available already as a subtype within the bar charts. I am not a relative of Stephen Few, but I think the bullet graph is a very interesting space efficient chart.


                                See attached for an example bullet graph.

                                But I don't think this will help you. The problem is that this approach (the same as the one used in the link and in the finance controlling demo), lets you create single bullet charts, NOT bullet charts that are part of a chart with multiple rows. The finance controlling demo LOOKS like the bullet graphs are part of a bigger chart, but that's only because the person carefully lined up a lot of separate charts and text boxes and so on to LOOK like it's one chart.

                                Now, first, that's a TON of work to go to. But more importantly, it only works if the rows of your chart are fixed, and not dimension values that you can select and change. It appears to me that your rows are project and task details. These are almost certainly dimension values and selectable. You almost certainly want your graph to respond, so that if someone selects Project 2, you only show that project and its details.

                                Thus me pinning my hope on extensions. I'm pretty sure you could make a bullet graph extension, and that people already have. I'm less confident that you could use this as a representation for data in a straight table. I'm doubtful, actually. But I could be wrong, and it's the best approach I've thought of.

                                I think bullet charts should be supported, but I'm personally not a fan because I think they are largely meaningless to untrained people on first glance (and probably on second and third). Yes, it's a space-efficient chart for displaying a lot of information, but that is its weakness as well as its strength - there's just TOO MUCH information in too dense a region for most users. There is also no natural interpretation like "oh, obviously that tick mark is such and such". I can't even remember myself, and I've read a couple of Stephen's books and fiddled with bullet graphs in QlikView. When you densely pack information, that information has to be obvious. I just don't think it is in a bullet graph.

                                I think the graph you showed is a nice compromise, though, if you can get it working. There are only two pieces of information in each instead of four. I think that makes it simple enough to be clear and readable for an untrained user.

                                  • Stacked Bars - How to display one thinner bar inside another (wider) bar ?
                                    Enrique Herranz

                                    Thanks for the link to the video Jean, in fact I had seen that video already. Unfortunately, and as John explains, the problem with that "trick" to build Bullet Graphs by overlaying a bar and a gauge is that only works as an individual chart.

                                    Also, as John says, it´s too much work to set it up. It´s a real pitty that it´s not available as a normal bar chart that you can configure for multiple expressions ( so you can interact with the chart as usual and make selections so the chart changes accordingly ).

                                    John, regarding your comments about the bullet being meaningless, I think if you don´t overcomplicate the bullet it can be very efficient and easy to read I was planning to use a relative scale so I could see hours Percentage (%) ( Actual hrs vs Plan hours ) so I could easily see when I am running out of budget ( bar entering the caution zone ) and also when I am overbudget ( bar crossing the target and entering the red zone ). See attached pictures to illustrate this better. I really think that chart would be very easy to interpret.

                                     

                                    Thank you all for your comments and help.

                                     

                                     

                                      • Stacked Bars - How to display one thinner bar inside another (wider) bar ?
                                        John Witherspoon

                                        That looks reasonably easy to read, but there seems little need for the tick mark at 100% (if all it is is identifying 100%). In fact, if you display everything as percentage instead of as hours, this would be easily handled with a linear gauge, and lets you much more easily compare which tasks are closer to complete or more over budget than others. See attached as one possible way to display the data.

                                          • Stacked Bars - How to display one thinner bar inside another (wider) bar ?
                                            Enrique Herranz

                                            Thanks John. You are right, the tick mark is kind of redundant when placed at 100% ( where the red zone starts ). I was playing with different visualizations, and I also found the linear gauge as the "closest" to the bullet graph. See my attached sample. In my case I need to monitor and compare 3 different let´s say "type" of labour hours : the baseline ( As_Sold hours ), the EAC hrs ( Estimate at Completion ) or Plan hrs , and the Actual hours. So I need to close track that my actual spent hrs are never higher than my Plan hrs ( adjusting the Plan when needed ). Then I check the deviation between my Baseline hours and the current Plan ( the forecasted estimate at completion ).

                                            By the way, how do you display the 0%, 90%, 100%,150% on top of the linear gauge ( I can´t find where to set it in the gauge settings ) ?

                                            Thanks

                                             

                                              • Stacked Bars - How to display one thinner bar inside another (wider) bar ?
                                                Jonas Sandström

                                                The % scale on the top is a text object w/ spaces separating the 0% 90% etc.

                                                  • Stacked Bars - How to display one thinner bar inside another (wider) bar ?
                                                    Enrique Herranz

                                                    Thanks Jonas, I just saw the trick ...the only "challenge" I guess is to keep the overlayed text perfectly aligned within the table. I have seen John also added the little vertical lines below the % numbers. I will give it a try and see if it doesn´t give me problems ( in case the table gets resized for example ).

                                                    Enrique

                                                      • Stacked Bars - How to display one thinner bar inside another (wider) bar ?
                                                        John Witherspoon

                                                         


                                                        Enrique wrote:the only "challenge" I guess is to keep the overlayed text perfectly aligned within the table. I have seen John also added the little vertical lines below the % numbers. I will give it a try and see if it doesn´t give me problems ( in case the table gets resized for example ).


                                                        Normally what I do when I build a chart out of multiple objects is to remove the checkmark next to "Allow Move/Size" on the Layout tab for every object in the chart. It's not as flexible for the users, but at least you have full control over how it looks (until users start zooming, but lots of things look bad when users zoom). So that's one option.

                                                        Other options I considered but discarded while building the example:

                                                        - Just describing the colors with multiple lines of text in the column heading (takes more space, not as clear)
                                                        - Creating a legend of the colors off to the side of the chart (takes more space, not as clear)
                                                        - Using "Show Scale" in the gauge settings (takes MUCH more space, didn't see how to show scale in %)

                                                        I'm testing my example with zoom, and while the vertical lines stay roughly where they should, the numbers do not. I suspect that's because the text does not smoothly zoom, but simply chooses the closest whole font size. That's useless for us. The fix would probably be to use a separate text object for each number. It would probably still have stuff a pixel or two off when zoomed, but it at least would be a lot closer.

                                                        Edit: Yeah, separate text boxes work much better with zoom. See attached for that and disallowing move and size. The user can still resize and drag columns around, though, which will still throw off the scale, so it's definitely not perfect. But it's probably what I would do in practice if I didn't think of any better idea (and I haven't yet). Now you could also overlay the whole thing with a transparent text box to keep them from even fiddling with the columns, but I'd prefer to avoid that, since I think fiddling with columns is rather uncommon, while making selections in a chart is probably pretty common. So I'd like to continue to support making selections in a column, even if it means the user CAN mess up the carefully-positioned scale. Hmmm, well, we can disallow drag and drop on the Presentation tab if we want, but I'm still not seeing a way to prevent resizing of columns.

                                                    • Stacked Bars - How to display one thinner bar inside another (wider) bar ?
                                                      Jonas Sandström

                                                      The % scale on the top is a text object w/ spaces separating the 0% 90% etc.

                                    • Stacked Bars - How to display one thinner bar inside another (wider) bar ?
                                      Jean-Jacques Jesua

                                      Hi,

                                      have a look at this [View:http://andpointsbeyond.com/2008/05/02/create-a-bullet-graph-in-qlikview-video/:550:0]

                                      It explains how to create a bullet chart, ovelaying 2 charts a gauge & a bar chart.

                                      it's a little bit tricky & not user friendly. Maybe in a new QV version Bullet chart will appear as new object

                                      JJ

                                      • Stacked Bars - How to display one thinner bar inside another (wider) bar ?
                                        Martin Wigchert

                                         

                                        Think you can get pretty close to what you want (but not exactly...), with the standard possibilities of QV 9.

                                        If you have two expressions (planned and actuals), you can define the following Presentation properties of the bar chart:

                                        - Bar distance: set to minimum value: -6

                                        - Cluster distance: set to maximum value: 8

                                        Choose the Thin Bars as well.

                                        Try the different bar chart types to find the one that you like best

                                        Hope this helps....

                                         

                                        Martin