topic Conditional formatting based on two selections in QlikView

Conditional formatting based on two selections

ZoeM — Wed, 25 Nov 2020 16:16:04 GMT

Hi Community.

I have Text Box which I want to Conditional format based on the selected in field action.

The Action is to Select two values (text values) and I would like it to format the text in the text box Blue. I tired using the If function below and various combinations of it but it cant seem to recognize what I have in parentheses which tells me my syntax is wrong:

=if(Upcoming_Sales=('One Week','Two Week'),RGB(0,176,240),RGB(54,54,54))

The text box is showing the sales coming up for the next two weeks and I want the font color and box to conditionally change when selected to Blue.

Any and all help will be truly appreciated.

Re: Conditional formatting based on two selections

sunny_talwar — Mon, 23 Jul 2018 16:50:44 GMT

Try this

=If(Match(Upcoming_Sales, 'One Week','Two Week'), RGB(0,176,240), RGB(54,54,54))

Re: Conditional formatting based on two selections

ZoeM — Mon, 23 Jul 2018 17:40:30 GMT

Thanks Sunny, but unfortunately it didn't work. Though QVW did like the formula, it did not format the font as desired.

Re: Conditional formatting based on two selections

ZoeM — Mon, 23 Jul 2018 17:42:10 GMT

I did find this though and it works like a charm for two values:

If(Concat(DISTINCT Upcoming_Sales,',')='One Week,Two Week',RGB(0,176,240),RGB(54,54,54))

But when I try to add a third value 'Four Week', it does not seem to work.

Any ideas on how I can add a third value?

Re: Conditional formatting based on two selections

sunny_talwar — Mon, 23 Jul 2018 17:45:28 GMT

I believe the Concatenation is done in ascending order of the text... may be try this

If(Concat(DISTINCT Upcoming_Sales,',') = 'Four Week,One Week,Two Week',RGB(0,176,240),RGB(54,54,54))

Re: Conditional formatting based on two selections

sunny_talwar — Mon, 23 Jul 2018 17:47:00 GMT

Better still would be to do this

If(

SubStringCount(Concat(DISTINCT Upcoming_Sales,','), 'One Week') = 1 and

SubStringCount(Concat(DISTINCT Upcoming_Sales,','), 'Two Week') = 1 and

SubStringCount(Concat(DISTINCT Upcoming_Sales,','), 'Four Week') = 1,

RGB(0,176,240), RGB(54,54,54))

Re: Conditional formatting based on two selections

ZoeM — Mon, 23 Jul 2018 17:54:04 GMT

As usual Sunny, you fail to disappoint

This works like a charm