topic Re: Background Color Expression w/multiple IFs in QlikView

Background Color Expression w/multiple IFs

Thu, 02 Oct 2014 14:01:25 GMT

Couldn't find an answer to this via online search or this site. I'm getting "Expression OK" with the below code when trying to modify the Background Color on an expression but it's not changing anything. Please help! I've tried it with and without the equals sign...

=if(Status='Not Started' and START_QUARTER>END_QUARTER,RGB(255,153,153),

if(Status='Started' and START_QUARTER>END_QUARTER and IsNull(COMPLETION_DATE),RGB(255,153,153),
if(Status='In Progress',RGB(153,255,153),RGB(255,255,255))))

Re: Background Color Expression w/multiple IFs

m_woolf — Thu, 02 Oct 2014 14:09:22 GMT

When you say yoou are not getting anything, I presume you mean that you are getting a background color of White.

Try changing RGB(255,255,255) to some other value to see if the logic is always getting to the default color.

Re: Background Color Expression w/multiple IFs

Thu, 02 Oct 2014 14:26:41 GMT

Brilliant approach! This did change the cell colors (tried 0,255,0). Still having problems, though. The last RGB value and the first one show up, none of the others though.

Re: Background Color Expression w/multiple IFs

m_woolf — Thu, 02 Oct 2014 14:39:23 GMT

You should reverse the first and second IFs. The second IF is more specific than the first. If it is not true, then ask the more general question.

I have no idea why you are not getting anything for the 'In Progress' IF.

Re: Background Color Expression w/multiple IFs

datanibbler — Thu, 02 Oct 2014 14:56:32 GMT

Try using the PICK(MATCH() function instead. That will get you rid of all the closing brackets, your formula will be more robust and less error-prone.

Re: Background Color Expression w/multiple IFs

Thu, 02 Oct 2014 15:15:48 GMT

The In Progress portion was a typo on my part. Rearranging the statements seemed to work. Just for future reference, though, if statements should be in order from specific to vague?

Re: Background Color Expression w/multiple IFs

Thu, 02 Oct 2014 15:22:13 GMT

Thanks I'll look into PICK MATCH.

Re: Background Color Expression w/multiple IFs

m_woolf — Thu, 02 Oct 2014 15:26:24 GMT

If you ask the more general question first and it is true, you never make it to the more specific question.

If I ask you:

If You are Human then X,

else If you are Human and Male then Y

I will always get X as the result.