Conditional Formatting table with a range

[#1]

Hi there,

I have a table with a % Net recovery column in. I want to be able to colour this column three different colours based on the value.

0-69% Red

70-99% Orange

100%+ Green

I have done this successfully for <70% being Red otherwise everything else is Green as below and all works (the expression is further complicated by the complex data I have!):

if( num(

Pick(CompIndex

,

((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

  +

      SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])

  )/

      sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

  )

  ,

   ((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

  +

      SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])

  )/

      sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

  ))

  ,'##0%') <0.70,RgbRed,RgbDarkGreen)

Would anyone be able to help me with adding the additional logic for the mid range? Is it possible in Qlik Sense?

Thank you,

Emma

[miguelbraga]

I believe this is what you require:

if( num(

Pick(CompIndex

,

((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

  +

      SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])

  )/

      sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

  )

  ,

   ((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

  +

      SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])

  )/

      sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

  ))

  ,'##0%') <0.70,

if( num(

Pick(CompIndex

,

((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

  +

      SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])

  )/

      sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

  )

  ,

   ((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

  +

      SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])

  )/

      sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

  ))

  ,'##0%') >= 0.70 and

num(

Pick(CompIndex

,

((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

  +

      SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])

  )/

      sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

  )

  ,

   ((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

  +

      SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])

  )/

      sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

  ))

  ,'##0%') < 1,

RgbRed,RgbDarkGreen

)

)

Hope this helps

Regards,

MB

PS: (WARNING) didn't tested it, may have problems with parenthesis

[#3]

Thank you Miguel. I am not sure I see where the Orange (RgbOrange in my case) will come in?

I should have added to my original post that these are custom colours I am trying to apply.

[miguelbraga]

I'm sorry, let me correct it

if( num(

Pick(CompIndex

,

((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

  +

      SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])

  )/

      sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

  )

  ,

   ((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

  +

      SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])

  )/

      sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

  ))

  ,'##0%') <0.70,

RgbRed

if( num(

Pick(CompIndex

,

((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

  +

      SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])

  )/

      sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

  )

  ,

   ((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

  +

      SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])

  )/

      sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

  ))

  ,'##0%') >= 0.70 and

num(

Pick(CompIndex

,

((Sum({<$(vCurrentFYSet)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

  +

      SUM({<$(vCurrentFYSet)>} [Revenue Amount - $(=only(ChooseCurrency))])

  )/

      sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

  )

  ,

   ((Sum({<$(vCurrentR12Set)>} [Amount - $(=only(ChooseCurrency)) - ThirdPartyCosts])

  +

      SUM({<$(vCurrentR12Set)>} [Revenue Amount - $(=only(ChooseCurrency))])

  )/

      sum( {<$(vCurrentR12Set), ProjectTypeCode -= {'NC', 'IC'}>} [Amount - $(=ChooseCurrency) - TimeCosts])

  ))

  ,'##0%') < 1,

RgbOrange,

RgbDarkGreen

)

)

This way is done I didn't saw the Orange RGB.

Hope this solves your issue.

Regards,

MB