topic Custom bar chart coloumn based on reference line in QlikView

Custom bar chart coloumn based on reference line

Tue, 23 Nov 2010 12:47:41 GMT

Hi Community,

I would like to ask if this is possible in bar chart.

My chart is like this:

I want to change the color of the part of the bar that exceeds the reference line like this:

Is this possible? Any idea or help is much appreciated.

Thanks!

Nadsky

Custom bar chart coloumn based on reference line

celindho — Tue, 23 Nov 2010 13:55:08 GMT

Hi!

As far as I know, this cannot be done directly but there are workarounds.

The simplest would be to make a stacked bar chart:

Assuming your expression in the chart is sum(Allocation) and your reference line is set at referenceValue, you can create two expressions, one for the blue part and one for the red part.

First expression: min( sum(Allocation), referenceValue) - The "min" prevents the bar from growing over the reference line

Second expression: max(sum(Allocation) - referenceValue, 0) - If the original expression value is less or equal to the reference line, we get a value of 0, otherwise we get the part that exceeds the reference line

By coloring the first blue and the second red, you get the desired effect. I think my solution would not work with negative or mixed sign values, but shouldn't be too hard to modify for that case also.

Hope this helps!

-Christian

Custom bar chart coloumn based on reference line

Wed, 24 Nov 2010 13:16:26 GMT

Hi Christian,

Thanks for the idea of using stack bar chart. The min max function seem not working on my expression (allocation is a formula sum(field1)/sum(field2) ). I used if-else condition instead.

Thanks for helping me out! 😄

Custom bar chart coloumn based on reference line

celindho — Wed, 24 Nov 2010 13:41:24 GMT

Glad that my suggestion got you on the right track.

Did you try to put the sum(filed1)/sum(field2) part into the min or max functions?

Custom bar chart coloumn based on reference line

celindho — Thu, 03 May 2012 13:17:35 GMT

Now i realized that you actually have to use the rangemin and rangemax functions instead of min and max. That way you get it working without the if statements.