Threshold colors

[#1]

I want to create a threshold level for the operating margin which is color coded. For example, i want the area of the bar below 5% to be red in color and the area in between 5% and 15% to be another color and everything above 15% in another color.

Please give the steps i need to follow and the codes i need to enter to accomplish this task as im quite new to QlikView

Thank You

[#2]

Hi,

here are the next one.

Good luck!

Rainer

[#3]

Now my graph looks like this. The code i used was

if(sum(serviceincome)/sum(servicesaleamt)/10>0.2,Green(),if(sum(serviceincome)/sum(servicesaleamt)/10<0.1,Red(),Yellow()))

i typed this out in the Expression>Background color input field.

But i would like only the part of the bar above and below the threshold limit to be colored and not the entire bar.

Any idea on how this can be done?

[#4]

Hi,

unfortunately not.

Rainer

[#5]

Hi

I would suggest the way to achieve this is to split your expression into three and produce a stacked bar chart.

First expression would be something like:

=IF(Sum(ServiceIncome)/Sum(ServiceSaleAmt)/10>0.1,0.1,Sum(ServiceIncome)/Sum(ServiceSaleAmt))

Background colour for this would be red, the idea of this is to limit the bar size to 10% if it is above 10%.

Second expression would be:

=IF(Sum(ServiceIncome)/Sum(ServiceSaleAmt)/10>0.2,0.1,IF(Sum(ServiceIncome)/Sum(ServiceSaleAmt)>0.1,Sum(ServiceIncome)/Sum(ServiceSaleAmt)-0.1,0)))

Background colour would be Yellow, the idea again is to limit the bar size to 10% when the total is above 20% and when its between 10% & 20% then just give the difference otherwise it should be zero, when stacked this should appear on top of the Red segment.

Third expression should be:

=IF(Sum(ServiceIncome)/Sum(ServiceSaleAmt)>0.2,Sum(ServiceIncome)/Sum(ServiceSaleAmt)-0.2,0)

Background colour should be green, it should only produce a value if the total is above 20% and then it should be the value over 20% (hence the -0.2 at the end).

Hope this helps!

[#6 · Accepted solution]

Hi

That was good thinking. However, i seem to have gotten better results when i used these expressions

=IF(Sum(serviceincome)/Sum(servicesaleamt)/10<0.1,Sum(serviceincome)/Sum(servicesaleamt)/10,0.1)

=IF(Sum(serviceincome)/Sum(servicesaleamt)/10>0.1,IF(Sum(serviceincome)/Sum(servicesaleamt)<0.2,Sum(serviceincome)/Sum(servicesaleamt)-0.1,0.1),0)

=IF(Sum(serviceincome)/Sum(servicesaleamt)/10>0.2,IF(Sum(serviceincome)/Sum(servicesaleamt)<0.3,Sum(serviceincome)/Sum(servicesaleamt)-0.2,0.1),0)

And so on...

Let me know if there is anything wrong in the above expressions.

thanks anyway

[#7]

Hi

Looks good to me, good job.