Background color expression with a replacing value

andymanu — Mon, 26 Jan 2026 18:19:17 GMT

Hi All,

Apologies if the heading sounds awkward.

Actually, Ii want to apply an expression to a column named Variance (Budget Cost - Actual Cost). I am able to apply the expression correctly.

My background color expression is,

=If(Sum([Current Month Actual Cost])/Sum([Current Month Budget Cost]) > 1.25, Red(),

If(Sum([Current Month Actual Cost])/Sum([Current Month Budget Cost]) > 1.00, LightRed(),

Green() ))

I am using as a fraction so that I could represent the magnitude of the variance as a proportion to the Budgeted cost.

Despite all works well, I came across the below issue,

There are certain budgeted cost values where the value is "0" (zero). In a so called situation, the calculation of the variance is correct (which is -9500) but, the background color should be "Red" instead of "Green" ( Because it divides the "9500" by "0").

May be an option to resolve this issue is to replace all the budgeted "0" values by "1" and performa the above said background expression.

However, I am not so sure how to perform the said task in the same background expression window (where I have written the above expression) so that, i will depict the correct variance values without harming the actual variance data along with the correct background color.

Could anyone help me in this regards?

Thank you in advance.

Kind regards,

Andrew

Re: Background color expression with a replacing value

Sergey_Shuklin — Tue, 09 Oct 2018 05:00:41 GMT

Hello!

You can increase your denominator by 0.0000001 for avoiding "division by zero" issues. Like that:

Sum([Current Month Actual Cost])

( Sum([Current Month Budget Cost]) + 0.0000001)

It won't harm your calculations much and a background color should be all right.

Re: Background color expression with a replacing value

andymanu — Tue, 09 Oct 2018 05:10:22 GMT

Hi Sergey,

Thank you very much for your much valuable logic.

It's absolutely correct and got the desired results.

Thanks again.

Kind Regards,

Andy

topic Re: Background color expression with a replacing value in QlikView

Background color expression with a replacing value

Re: Background color expression with a replacing value

Re: Background color expression with a replacing value