topic Re: Bar chart with overlaped bar in QlikView

Bar chart with overlaped bar

Anonymous — Tue, 12 Dec 2017 15:04:09 GMT

Hello,

Do you know if it's possible to do this kind of graph ? (with for each month, 2 différents expressions at the same place but with different bar witdh)

Many thanks in advance

Re: Bar chart with overlaped bar

m_woolf — Tue, 12 Dec 2017 17:08:28 GMT

Could be done by overlaying 2 charts.

Re: Bar chart with overlaped bar

MarcoWedel — Tue, 12 Dec 2017 21:26:31 GMT

Maybe this helps

https://community.qlik.com/search.jspa?q=bullet+chart

regards

Marco

Re: Bar chart with overlaped bar

Anonymous — Thu, 14 Dec 2017 10:07:24 GMT

Hello,

I already tried it but I can't change the bar width for the second graph.

Re: Bar chart with overlaped bar

MarcoWedel — Thu, 14 Dec 2017 21:24:00 GMT

Hi,

one solution to turn this chart

into this one

could be:

hope this helps

regards

Marco

Re: Bar chart with overlaped bar

sunny_talwar — Thu, 14 Dec 2017 21:27:12 GMT

Wow!!!!, how is this thing even working? I don't understand what you have done . I feel dumb asking, but can you explain the settings a little more (although you have already showed all of them in the image above)

Re: Bar chart with overlaped bar

MarcoWedel — Thu, 14 Dec 2017 21:48:01 GMT

Hi Sunny,

I'm glad you like it.

It just struck me that one could make a group of bars appear as one single bar when reducing the space in between to zero.

So I used a second calculated ValueLoop dimension to create clusters of three stacked bars each.

The side bars (1 and 3) here only get a value for the second expression, while the middle bar has values for both the first and second expression. The second expression then is reduced by the first expression value to let the middle stack height equal the side bar height in cases where the first expression is less than the second one.

I'm not convinced this explains it well enough ...?

Marco

Re: Bar chart with overlaped bar

Anonymous — Thu, 14 Dec 2017 21:54:45 GMT

That's brillant!

Re: Bar chart with overlaped bar

MarcoWedel — Thu, 14 Dec 2017 21:59:30 GMT

thanks

Re: Bar chart with overlaped bar

sunny_talwar — Fri, 15 Dec 2017 01:40:56 GMT

It does and it is amazing what you did marcowedel‌. You are amazing

Re: Bar chart with overlaped bar

Anonymous — Fri, 15 Dec 2017 07:49:35 GMT

very ingenious !

many thanks

Re: Bar chart with overlaped bar

pnn44794 — Fri, 15 Dec 2017 18:52:10 GMT

Nice work. Is if possible to have three bars overlapping, i.e. Value1, Value2 and Value3? If yes, what change or addition would need to be made?

Re: Bar chart with overlaped bar

MarcoWedel — Fri, 15 Dec 2017 21:07:29 GMT

thanks,

can you describe how the three bar version should look like?

Re: Bar chart with overlaped bar

pnn44794 — Fri, 15 Dec 2017 21:49:16 GMT

Hello Marco:

Thanks for the reply. First, this is pretty cool and clever. Nice job. I’m thinking, though it may not be doable, is that you may be able to adjust the Cluster Distance for the third value to accommodate it??? The more I think about it, the more I think this can’t be done with more than 2 values. So, as you have now, if value 2 is larger than value 1, it’s bar-width is wider than value 1’s. Value 3’s would need to be wider than value 2’s. I get it though or I’m starting to, that this probably can’t be done or at least not natively to Qlikview.

Thanks again for the original solution!

Re: Bar chart with overlaped bar

MarcoWedel — Fri, 15 Dec 2017 22:53:53 GMT

I guess a three bar version might work like this:

hope this helps

regards

Marco

Re: Bar chart with overlaped bar

pnn44794 — Sat, 16 Dec 2017 00:15:17 GMT

YES!!!! Awesome. Going more than 3 bars, probably wouldn't be a good idea, but think about what you can show with three. Previous year, current year and future year, for example. Ok. Maybe not a good example, but the point is when you have limited screen real estate, being able to show 3 things in one chart can be a big deal. I know there may be other ways to do it, but this is a pretty cool way to do so using a bar chart. The key, obviously, is to use the right Value X's. So you may have to set your data up appropriately, but you've solved the hard part once you have.

While I don't know if anyone else has ever done this in Qlikview or not, you may be able to call this the Wedel Bar Chart. Well done and thank you very much!

Re: Bar chart with overlaped bar

MarcoWedel — Sat, 16 Dec 2017 00:55:20 GMT

Thanks, I'm glad you find it useful.

Although agreeing that it probably doesn't make much sense to go beyond 3 bars, I couldn't resist to see how it would look like 😉

Here's one example using 6 bars

(Based on its looks I was about to propose a name change into "Manhattan bar chart" until realizing that it could be mistaken for the 3D bar chart version already named like that, but maybe "2D Manhattan bar chart" might do?)

Despite possible applications of the chart itself, maybe the used expressions could help someone:

Expressions 1 to 6:

Pick(fabs(ValueLoop(1,11)-6)+1,
    Sum(Value1)
    )

Pick(fabs(ValueLoop(1,11)-6)+1,
    RangeMax(Sum(Value2)-Sum(Value1),0),
    Sum(Value2)
    )

Pick(fabs(ValueLoop(1,11)-6)+1,
    RangeMax(Sum(Value3)-RangeMax(Sum(Value1),Sum(Value2)),0),
    RangeMax(Sum(Value3)-Sum(Value2),0),
    Sum(Value3)
    )

Pick(fabs(ValueLoop(1,11)-6)+1,
    RangeMax(Sum(Value4)-RangeMax(Sum(Value1),Sum(Value2),Sum(Value3)),0),
    RangeMax(Sum(Value4)-RangeMax(Sum(Value2),Sum(Value3)),0),
    RangeMax(Sum(Value4)-Sum(Value3),0),
    Sum(Value4)
    )

Pick(fabs(ValueLoop(1,11)-6)+1,
    RangeMax(Sum(Value5)-RangeMax(Sum(Value1),Sum(Value2),Sum(Value3),Sum(Value4)),0),
    RangeMax(Sum(Value5)-RangeMax(Sum(Value2),Sum(Value3),Sum(Value4)),0),
    RangeMax(Sum(Value5)-RangeMax(Sum(Value3),Sum(Value4)),0),
    RangeMax(Sum(Value5)-Sum(Value4),0),
    Sum(Value5)
    )

Pick(fabs(ValueLoop(1,11)-6)+1,
    RangeMax(Sum(Value6)-RangeMax(Sum(Value1),Sum(Value2),Sum(Value3),Sum(Value4),Sum(Value5)),0),
    RangeMax(Sum(Value6)-RangeMax(Sum(Value2),Sum(Value3),Sum(Value4),Sum(Value5)),0),
    RangeMax(Sum(Value6)-RangeMax(Sum(Value3),Sum(Value4),Sum(Value5)),0),
    RangeMax(Sum(Value6)-RangeMax(Sum(Value4),Sum(Value5)),0),
    RangeMax(Sum(Value6)-Sum(Value5),0),
    Sum(Value6)
    )

Marco

Re: Bar chart with overlaped bar

pnn44794 — Sat, 16 Dec 2017 01:17:07 GMT

Wow! The expressions will absolutely help someone.............like me. This is, again, awesome. More than 3 bars is probably too much. Think about senior management. It's hard to keep their attention as it is, so the more complicated, the less likely they are to pay attention, if you get my drift. With that said though, I do see a potential use for more than 3 bars. For example if you have to do some type of multi-year comparison of actuals (I know it can be done other ways, but if real estate is limited in your display.....).

This is a "great to have in your back pocket bar chart" that can be pretty easily put together. I t would not have been easy without your info above. Again, nice work Marco!

I hate to ask this, so please excuse my lack of knowledge, but could you explain in layman terms, a short version, of what the above pick expressions are doing?

Re: Bar chart with overlaped bar

pnn44794 — Sat, 16 Dec 2017 01:19:14 GMT

P.S. 2d Manhattan I suppose would work, but can we get your name on it??? This is really nice work within Qlikview.

Re: Bar chart with overlaped bar

MarcoWedel — Sat, 16 Dec 2017 01:49:54 GMT

Once again thanks for your kind reply.

The idea behind those expressions is to calculate the height that each bar towers above the highest bar in front of it.

The Pick()‌ function switches between the expressions corresponding to the cluster segments, with the "Fabs(ValueLoop..." part taking advantage of the cluster symmetry, i.e. bar segments in the same distance from the middle segment to the left or right share common height/expression.

hopefully this makes any sense.

Marco