topic Conditional Formatting table with a range in App Development

Conditional Formatting table with a range

Mon, 18 Jul 2016 11:05:23 GMT

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

Re: Conditional Formatting table with a range

miguelbraga — Mon, 18 Jul 2016 11:54:15 GMT

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,

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

Re: Conditional Formatting table with a range

Mon, 18 Jul 2016 11:57:48 GMT

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.

Re: Conditional Formatting table with a range

miguelbraga — Mon, 18 Jul 2016 15:09:20 GMT

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,