Qlik Community

QlikView Extensions

Discussion Board for collaboration on QlikView Extensions.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Problem with formula for setting color in Textbox

Hello everybody,

I have a problem with a formula. The formula looks like:

IF(index(concat(Datum_verg&Severity,','),'4NM')>0,Yellow(),

IF(index(concat(Datum_verg&Severity,','),'4I')>0,LightBlue(),

IF(index(concat(Datum_verg&Severity,','),'4A')>0,LightRed(),

IF(DAY(MakeDate(Year(Today()),Month(Today()),4))<DAY(Today()),LightGreen(),White()))))

For examplI if I have data in my table for the 24.01.2014 so Datum_verg will create with 24. The textbox of the 24 will mark with the color for Severity. But the Textbox for the 4 will also mark like the 24.

See for more information the attachment.

What can I do can anyone help me.

Thanks

grettings sven

1 Solution

Accepted Solutions
jerem1234
Valued Contributor II

Re: Problem with formula for setting color in Textbox

So your problem is with the formula marking both 4NM and 24NM with the same value? Why don't you create dummy characters in the beginning, middle and end like:

=IF(index(concat('|' &Datum_verg& '|' & Severity & '|',','),'|4|NM|')>0,Yellow(),

  IF(index(concat('|' &Datum_verg& '|' & Severity & '|',','),'|4|I|')>0,LightBlue(),

  IF(index(concat('|' &Datum_verg& '|' & Severity & '|',','),'|4|A|')>0,LightRed(),

  IF(DAY(MakeDate(Year(Today()),Month(Today()),24))<DAY(Today()),LightGreen(),White()))))

or maybe even better, use match function such as:

=IF(max(match(Datum_verg&Severity,'24NM')),Yellow(),

  IF(max(match(Datum_verg&Severity,'24I')),LightBlue(),

  IF(max(match(Datum_verg&Severity,'24A')),LightRed(),

  IF(DAY(MakeDate(Year(Today()),Month(Today()),24))<DAY(Today()),LightGreen(),White()))))

If this doesn't help, can you explain a little more of what you are trying to achieve?

Hope this helps!

5 Replies
giakoum
Honored Contributor II

Re: Problem with formula for setting color in Textbox

Try using subfield instead of index

Not applicable

Re: Problem with formula for setting color in Textbox

Can you please explaine or insert an example thanks

giakoum
Honored Contributor II

Re: Problem with formula for setting color in Textbox

so it does, because the search string is present in both string (4NM and 24NM both contain 4NM). Is there no other field  to use to determine?

jerem1234
Valued Contributor II

Re: Problem with formula for setting color in Textbox

So your problem is with the formula marking both 4NM and 24NM with the same value? Why don't you create dummy characters in the beginning, middle and end like:

=IF(index(concat('|' &Datum_verg& '|' & Severity & '|',','),'|4|NM|')>0,Yellow(),

  IF(index(concat('|' &Datum_verg& '|' & Severity & '|',','),'|4|I|')>0,LightBlue(),

  IF(index(concat('|' &Datum_verg& '|' & Severity & '|',','),'|4|A|')>0,LightRed(),

  IF(DAY(MakeDate(Year(Today()),Month(Today()),24))<DAY(Today()),LightGreen(),White()))))

or maybe even better, use match function such as:

=IF(max(match(Datum_verg&Severity,'24NM')),Yellow(),

  IF(max(match(Datum_verg&Severity,'24I')),LightBlue(),

  IF(max(match(Datum_verg&Severity,'24A')),LightRed(),

  IF(DAY(MakeDate(Year(Today()),Month(Today()),24))<DAY(Today()),LightGreen(),White()))))

If this doesn't help, can you explain a little more of what you are trying to achieve?

Hope this helps!

Not applicable

Re: Problem with formula for setting color in Textbox

Hi jerem,

I use the second function and it works. Thanks for your help.

I use this for a safteycross for our plant, so we have a table for all accidents in it. The colors ar standing for different severitys. So we can represent the achtually month on our teamboards

Greetings Sven