+ Start a Discussion
Sakthivel ThandavarayanSakthivel Thandavarayan 

Please correct my case formula,

Hi all,

My formula working fine for all the criteria except the last one, my criteria is,
If
Age in hours > 3 = Red 
Age in hours > 1.5 = Warning 
Age in hours < 1.5 = Green

severity = S1 or S2 and Age in hours > 6 = Red
severity = S1 or S2 and Age in hours > 3 = Warning
severity = S1 or S2 and Age in hours > 6 = Green

severity = S3 and Type= Data/Methodology and Age in hours > 24 = Red
severity = S3 and Type= Data/Methodology and Age in hours > 12 = Warning
severity = S3 and Type= Data/Methodology and Age in hours < 12 = Green

severity = S3 and Type= Functional and Age in hours > 744 = Red
severity = S3 and Type= Functional and Age in hours > 372 = Warning
severity = S3 and Type= Functional and Age in hours > 372 = Green

So i defined like this,


IF(Age_in_Hours__c > 3, IMAGE("/img/msg_icons/error16.png","Violation"), 
IF(Age_in_Hours__c > 1.5, IMAGE("/img/msg_icons/warning16.png","Warning"), 
IF(Age_in_Hours__c < 1.5, IMAGE("/img/msg_icons/confirm16.png","Success"), 

IF(AND((ISPICKVAL(Severity__c, 'S1')||(ISPICKVAL(Severity__c, 'S2'))),Age_in_Hours__c > 6), IMAGE("/img/msg_icons/error16.png","Violation"), 
IF(AND((ISPICKVAL(Severity__c, 'S1')||(ISPICKVAL(Severity__c, 'S2'))),Age_in_Hours__c > 3), IMAGE("/img/msg_icons/warning16.png","Warning"), 
IF(AND((ISPICKVAL(Severity__c, 'S1')||(ISPICKVAL(Severity__c, 'S2'))),Age_in_Hours__c < 3), IMAGE("/img/msg_icons/confirm16.png","Success"), 

IF(AND(ISPICKVAL(Severity__c, 'S3'),ISPICKVAL(Type, 'Data/Methodology'),Age_in_Hours__c > 24), IMAGE("/img/msg_icons/error16.png","Violation"), 
IF(AND(ISPICKVAL(Severity__c, 'S3'),ISPICKVAL(Type, 'Data/Methodology'),Age_in_Hours__c > 12), IMAGE("/img/msg_icons/warning16.png","Warning"), 
IF(AND(ISPICKVAL(Severity__c, 'S3'),ISPICKVAL(Type, 'Data/Methodology'),Age_in_Hours__c < 12), IMAGE("/img/msg_icons/confirm16.png","Success"), 

IF((ISPICKVAL(Severity__c, 'S3')&& ISPICKVAL(Type, 'Functional')&&Age_in_Hours__c > 744), IMAGE("/img/msg_icons/error16.png","Violation"), 
IF((ISPICKVAL(Severity__c, 'S3')&&ISPICKVAL(Type, 'Functional')&&Age_in_Hours__c > 372), IMAGE("/img/msg_icons/warning16.png","Warning"), 
IF((ISPICKVAL(Severity__c, 'S3')&&ISPICKVAL(Type, 'Functional')&&Age_in_Hours__c < 372), IMAGE("/img/msg_icons/confirm16.png","Success"), 

IMAGE("/img/msg_icons/confirm16.png","Success")))))))))))))

If severity = S3 and type = Functional and Age in hours < 372,
I'm getting red flag instead of green, not sure what I'm missing. 

Appreciate greatly if anyone can help me on fixing this or can provide simplified formula for this.

Thanks,
Sakthi 
Best Answer chosen by Sakthivel Thandavarayan
William TranWilliam Tran
That's happening because this IF ,which is highest in priority is likely true:

IF(Age_in_Hours__c > 3, IMAGE("/img/msg_icons/error16.png","Violation"),

You need to structure your ifs so that the top ones don't override or overshadow the bottom ones.

Right now ,your IFs are overlappings (more than one IF can be true), and in that case the top IF wins.

Your need to make it mutually exclusive

or make it so that you do want the top IF to win regardless
so in this case if Age_in_Hours__c > 3 --> violation, regardless of any other parameters.

Thx

All Answers

William TranWilliam Tran
That's happening because this IF ,which is highest in priority is likely true:

IF(Age_in_Hours__c > 3, IMAGE("/img/msg_icons/error16.png","Violation"),

You need to structure your ifs so that the top ones don't override or overshadow the bottom ones.

Right now ,your IFs are overlappings (more than one IF can be true), and in that case the top IF wins.

Your need to make it mutually exclusive

or make it so that you do want the top IF to win regardless
so in this case if Age_in_Hours__c > 3 --> violation, regardless of any other parameters.

Thx
This was selected as the best answer
Sakthivel ThandavarayanSakthivel Thandavarayan
There it is, I just reversed the order, I think its fine now. Thanks a lot :)