If Condition on 4 Columns

[kishoreravi1983]

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

RESULT

 Please help me on this regards.

Thank you!

Ravi

[lironbaram]

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]

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 · Accepted solution]

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]

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,

[Michiel_QV_Fan]

http://www.qlikblog.at/1384/set-analysis-wizard-qlikview/

[manoranjan_321988]

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