6 Replies Latest reply: Aug 10, 2017 9:15 AM by Alison Cooke RSS

    Colour Expression for Budget Variance

    Alison Cooke

      Hello,

       

      I would like some help in formulating a text colour expression.

       

      I want the text to be green when the variance ([Project Year Budget]) - sum([Project Expenditure Amount]) is less than the budget. But I also want the text to be red when the variance falls below zero. And, yellow when the variance is equal to the budget.

       

      I have tried to do so using this expression:

       

       

      if((sum([Project Year Budget]) - sum([Project Expenditure Amount])) < sum([Project Year Budget]), green (255)

       

       

      ,if((sum([Project Year Budget]) - sum([Project Expenditure Amount])) <'-1', red ()

       

       

      ,if((sum([Project Year Budget]) - sum([Project Expenditure Amount])) = sum([Project Year Budget]), RGB(255,128,0)

       

      )))

              

       

      The yellow and green text is working correctly. But I cannot work out how to get variance below zero to turn red. I imagine it is because a value below zero is also less than the project year budget and thus it turns green as it is commanded to do in the first line of the expression.

       

      How would I get it to work?

       

      Thank you in advance