Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
andymanu
Contributor

Background color expression with a replacing value

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

1 Solution

Accepted Solutions
Sergey_Shuklin
Valued Contributor

Re: Background color expression with a replacing value

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.

2 Replies
Sergey_Shuklin
Valued Contributor

Re: Background color expression with a replacing value

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.

andymanu
Contributor

Re: Background color expression with a replacing value

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