Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Barry_S
Contributor
Contributor

Multiple IF Statement with Set Analysis for Background Colour in Qlikview

Hi,

I'd appreciate some help with something if possible.

I'm attempting to evaluate an IF statement within Calculated condition for the background colour of a text field in Qlikview.

I have a number of ranges to colour for a RAG status as follows:

100% - 90% = Green

89% - 80% = Amber

<=79% = Red

I also have set analysis which calculates the field as follows, which evaluates correctly: ( I’ve changed the fields value for protection purposes )

num(Count ({$< SAFE = {'Yes'}, WORKS -= {'Unsafe'}>} ID )*100/Count({$< SAFE = {'Yes','No'}, WORKS -= {'Unsafe'}>} ID ),.00)

when I add in a single condition to test, it works as follows :

=IF
(num(Count ({$< SAFE = {'Yes'}, WORKS -= {'Unsafe'}>} ID )*100/Count({$< SAFE = {'Yes','No'}, WORKS -= {'Unsafe'}>} ID ),.00)<='79.00', RGB(255,128,64)
,RGB(255,79,79)
)

 

But when I then attempt to add the other RAG status into as additional IF’s, it doesn’t work and the final ‘)’ say’s there an error with it :

=if
(num(Count ({$< SAFE = {'Yes'}, WORKS -= {'Unsafe'}>} ID )*100/Count({$< SAFE = {'Yes','No'}, WORKS -= {'Unsafe'}>} ID ),.00)<='79.00', RGB(255,79,79),
if
(num(Count ({$< SAFE = {'Yes'}, WORKS -= {'Unsafe'}>} ID )*100/Count({$< SAFE = {'Yes','No'}, WORKS -= {'Unsafe'}>} ID ),.00)<='89.00',
and if
(num(Count ({$< SAFE = {'Yes'}, WORKS -= {'Unsafe'}>} ID )*100/Count({$< SAFE = {'Yes','No'}, WORKS -= {'Unsafe'}>} ID ),.00)>='80.00',RGB(255,128,64)
,RGB(255,79,79) )))

 

The ELSE Colour would therefore be anything >89 and would be Green.

I’ve tried so many different ways, and my next try is by adding the original and creating this as a Variable and adding the variable into the calculation, but wanted to make sure it worked by adding in the condition.

Thanks in advance for any help or assistance.

Barry_S

 

Pt 2, would be then to look at 3 individual box colours, to then calculate the overall RAG status of another box :

Eg. ( If any of the boxes are Red, then Red, If anything is Amber and not Red, then Amber, ELSE Green )

 

Labels (1)
1 Solution

Accepted Solutions
morgankejerhag
Partner - Creator III
Partner - Creator III

Your syntax is not correct. Simplified it should (?) be something like

if(sum(Value)<100,
   rgb(255,79,79),
   if(sum(Value)>=100 and sum(Value)<1000,
      rgb(255,128,64),
      rgb(255,79,79)
   )
)

View solution in original post

2 Replies
morgankejerhag
Partner - Creator III
Partner - Creator III

Your syntax is not correct. Simplified it should (?) be something like

if(sum(Value)<100,
   rgb(255,79,79),
   if(sum(Value)>=100 and sum(Value)<1000,
      rgb(255,128,64),
      rgb(255,79,79)
   )
)

Barry_S
Contributor
Contributor
Author

Thank you for the response, this was the correct solution thank you.