Nested If not working :(

[#1]

Hi there

I need some help with a nested If statement.  It's not giving me what I need.  I have two main criteria : CLOSED Status and ACTIVE status.  For CLOSED status there could only be two scenarios : OUT of breach and NOT breached.  For ACTIVE status theres are a few scenarios.  But for some reason all my ACTIVE scenarios are returned even when the Status is CLOSED. Here is my current Nested If.  Or maybe you can propose another method :

IF (UPPER(Status) = 'CLOSED' And (Amended_Duration) / Incident_SLA_Target_Day * 100  >= 95, 'SLA Breached',
IF (UPPER(Status) = 'CLOSED' And (Amended_Duration) / Incident_SLA_Target_Day * 100  < 95, 'Not Breached',
IF(Incident_SLA_Target_Day_New = 'Unknown' AND Status = 'ACTIVE', 'Unknown',
If(Incident_SLA_Target_Day_New = 'Awaiting Information', 'Awaiting Information',
If (Status = 'ACTIVE' AND (Amended_Duration) / Incident_SLA_Target_Day * 100  >= 95, 'SLA Breached',
IF (Status = 'ACTIVE' And (Amended_Duration) / Incident_SLA_Target_Day * 100  >= 75 And 
(Amended_Duration) / Incident_SLA_Target_Day * 100  < 95, 'Danger',
IF (Status = 'ACTIVE' And (Amended_Duration) / Incident_SLA_Target_Day * 100  < 75, 'Safe',
)
)
)
)
)
)
) As Incident_Final_SLA_Status

[robert_mika]

Could you post your data or qvw file?

[#3]

The IF statement looks good. The only thing I observed is that you have only used UPPER(Status) when CLOSED. Why is that so?

[#4]

Thats just one of the things I've tried when nothing else worked....  The original data only has Uppercase though, so I doubt it would make a difference.

[#5]

Try this..

If(Wildmatch(Incident_SLA_Target_Day_New,'Unknown'),'Unknown',If(Wildmatch(Incident_SLA_Target_Day_New,'Awaiting Information'),'Awaiting Information',
If(Wildmatch(Status,'CLOSED'),if(((Amended_Duration) / Incident_SLA_Target_Day * 100)>=95,'SLA Breached','Not Breached'),
If(Wildmatch(Status,'ACTIVE'),if(((Amended_Duration) / Incident_SLA_Target_Day * 100)>=95,'SLA Breached',if(((Amended_Duration) / Incident_SLA_Target_Day * 100)<75,'Safe','Danger'

))))))

But please have a check on the brackets and syntax.

Regards,

Dawar

[mrossoit]

Hi,

it's not clear how (Amended_Duration) / Incident_SLA_Target_Day * 100 has to be considered in combination of Incident_SLA_Target_Day_New value: when 'Unknown', when 'Awaiting Information' or could it assume other values?

Here a more compact version of your expression:

IF (UPPER(Status) = 'CLOSED', if((Amended_Duration) / Incident_SLA_Target_Day * 100  >= 95, 'SLA Breached', 'Not Breached'),

IF(Incident_SLA_Target_Day_New = 'Unknown', 'Unknown',

IF(Incident_SLA_Target_Day_New = 'Awaiting Information', 'Awaiting Information',

IF((Amended_Duration) / Incident_SLA_Target_Day * 100  >= 95, 'SLA Breached',

IF((Amended_Duration) / Incident_SLA_Target_Day * 100  >= 75 And (Amended_Duration) / Incident_SLA_Target_Day * 100  < 95, 'Danger', 'Safe'

))))) As Incident_Final_SLA_Status

Regards,

MR

[sagarkharpude]

Please attached the sample data

[#8]

I'll give this a go quickly... Thanks Mass

[#9]

Try this as well:

if((Amended_Duration) / Incident_SLA_Target_Day * 100 >= 95, 'SLA Breached',

  if(UPPER(Status) = 'CLOSED' AND (Amended_Duration) / Incident_SLA_Target_Day * 100  < 95, 'Not Breached',

  if(UPPER(Status) = 'ACTIVE' And (Amended_Duration) / Incident_SLA_Target_Day * 100  >= 75 And (Amended_Duration) / Incident_SLA_Target_Day * 100  < 95, 'Danger',

  if(UPPER(Status) = 'ACTIVE' And (Amended_Duration) / Incident_SLA_Target_Day * 100  < 75, 'Safe',

  If(Incident_SLA_Target_Day_New = 'Awaiting Information', 'Awaiting Information','Unknown')

  )

  )

  )

)