topic Threshold colors in QlikView

Threshold colors

Wed, 04 Nov 2009 12:49:41 GMT

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

Threshold colors

Wed, 04 Nov 2009 16:26:34 GMT

Hi,

here are the next one.

Good luck!

Rainer

Threshold colors

Wed, 04 Nov 2009 16:47:44 GMT

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?

Threshold colors

Wed, 04 Nov 2009 18:06:05 GMT

Hi,

unfortunately not.

Rainer

Threshold colors

Wed, 04 Nov 2009 18:12:39 GMT

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!

Threshold colors

Wed, 04 Nov 2009 20:57:58 GMT

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

Threshold colors

Wed, 04 Nov 2009 21:19:56 GMT

Looks good to me, good job.