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: 
kishoreravi1983
Contributor III
Contributor III

If Condition on 4 Columns

Hi Friends,

Please help me write a if condition for below scenario.

Attached the Data file.

Currenty i have tried this way..but its not coming correct:

If(ScopingComplete='Yes' or GovernanceComplete ='Yes' and ScopingStartDate= Null(),'Gray',
If(ScopingComplete='No' or GovernanceComplete ='No' and ScopingStartDate= Null(),'Blue',
If(ScopingComplete='No' or GovernanceComplete ='Yes' and NetWorkDays(ScopingStartDate, Today())>=0 and NetWorkDays(ScopingStartDate, Today()) <=10,'Green',
If(ScopingComplete='Yes' or GovernanceComplete ='No' and NetWorkDays(ScopingStartDate, Today())>10 and NetWorkDays(ScopingStartDate, Today())<=25,'Orange', 'Red')))) as NEW,

What i am getting is only two values in below table

RESULTRESULT

 Please help me on this regards.

Thank you!

Ravi

 

 

Labels (1)
1 Solution

Accepted Solutions
ashok_rajm
Contributor III
Contributor III

You can try below syntax.  This might help.

If(ScopingComplete='Yes' and GovernanceComplete ='Yes' and IsNull(ScopingStartDate),'Gray',
If(ScopingComplete='No' and GovernanceComplete ='No' and IsNull(ScopingStartDate),'Orange',
If(ScopingComplete='Yes' and GovernanceComplete ='No' and IsNull(ScopingStartDate),'Blue',
If(ScopingComplete='No' and GovernanceComplete ='Yes' and IsNull(ScopingStartDate),'Blue',
if(NetWorkDays(ScopingStartDate,Today())<=10,'Green',
   if(NetWorkDays(ScopingStartDate,Today())>10 and Days<=25,'Orange',
      if(NetWorkDays(ScopingStartDate,Today())>25,'Red'))))))) as New

 

 

View solution in original post

6 Replies
lironbaram
Partner - Master III
Partner - Master III

hi

you only get the two first values because you have the for option covered in the first two statements 

i assume you wanted to group the or statement and than check the and statement 

so you need to warp the or statement in brackets like this 

f((ScopingComplete='Yes' or GovernanceComplete ='Yes' )and ScopingStartDate= Null(),'Gray',

lironbaram
Partner - Master III
Partner - Master III

hi

you only get the two first values because you have the for option covered in the first two statements 

i assume you wanted to group the or statement and than check the and statement 

so you need to warp the or statement in brackets like this 

if((ScopingComplete='Yes' or GovernanceComplete ='Yes' )and ScopingStartDate= Null(),'Gray'

ashok_rajm
Contributor III
Contributor III

You can try below syntax.  This might help.

If(ScopingComplete='Yes' and GovernanceComplete ='Yes' and IsNull(ScopingStartDate),'Gray',
If(ScopingComplete='No' and GovernanceComplete ='No' and IsNull(ScopingStartDate),'Orange',
If(ScopingComplete='Yes' and GovernanceComplete ='No' and IsNull(ScopingStartDate),'Blue',
If(ScopingComplete='No' and GovernanceComplete ='Yes' and IsNull(ScopingStartDate),'Blue',
if(NetWorkDays(ScopingStartDate,Today())<=10,'Green',
   if(NetWorkDays(ScopingStartDate,Today())>10 and Days<=25,'Orange',
      if(NetWorkDays(ScopingStartDate,Today())>25,'Red'))))))) as New

 

 

kishoreravi1983
Contributor III
Contributor III
Author

Hi Ashok,
with a Bit change in the code the solution is working fine now. Thanks a lot for quick reply
If(ScopingComplete='Yes' and GovernanceComplete ='Yes' ,'Gray',
If(ScopingComplete='Yes' and GovernanceComplete ='No' and IsNull(ScopingStartDate),'Blue',
If(ScopingComplete='No' and GovernanceComplete ='Yes' and IsNull(ScopingStartDate),'Blue',
If(ScopingComplete='No' and GovernanceComplete ='No' and IsNull(ScopingStartDate),'Blue',
If(NetWorkDays(ScopingStartDate, Today())>=0 and NetWorkDays(ScopingStartDate, Today()) <=10,'Green',
If(NetWorkDays(ScopingStartDate,Today())>10 and NetWorkDays(ScopingStartDate, Today())<=25,'Orange',
If(NetWorkDays(ScopingStartDate,Today())>25,'Red'))))))) as NEW,

manoranjan_321988
Contributor
Contributor

the reason why its faling is because you are using OR

example 1

0 or 1 then 1->true

1 or 0 then 1 ->true

always your condition will be true

so if you use and

then it will be good

example 2:

0 and 1 only if condition true it will be true or else it will be false

1 and 0 only if condition true it will be true or else it will be false

 

Regards

Mano