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

[quiquehm]

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

[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

[quiquehm]

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.

[johnw]

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.

[quiquehm]

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

[saxjonas]

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

[saxjonas]

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

[quiquehm]

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

[johnw]

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.

[quiquehm]

John, thanks a lot for your time and all the suggestions. I just checked your sample, and you are right, separate text boxes work much better when you zoom in.

Thanks again

Enrique