Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
selvakumar Anbazhaganselvakumar Anbazhagan 

Opportunity Formula

Hi All,

I am working on opportunity custom formula field that need to flag the Older opportunities. My filter criteria is given below.

-    Opportunity is owned by Inbound Sales
-    Opportunity is SMB 
-    Opportunity Age is 60 days or older
-    Opportunity is in an open status (not Closed Won or Closed Lost)
-    Contract hasn’t been signed
-    Override hasn’t been triggered by management

I used the following formula : 

OR( 
AND( 
Cleanup_Category__c = 1, 
Owner.Profile.Name = "Inbound Sales", 
Opportunity_Age__c > 60, 
Probability < 0.91, 
Probability > 0.01, 
Inactive_Override__c = FALSE 
)

Def:

Cleanup_Category__c = 1 - Refers SMB
Inactive_Override__c = FALSE - This is revert back for the management changes.

But this doesn't flag all the possible old opportunities. I might stucked up some logic in my formula. I suspect it can be either "OR/AND" condition or else Probability (Since Closed Won (100%) and Closed Lost (0%)). Is any other way to acheive this. Any help on this will be great.

Thanks in advance.
Best Answer chosen by selvakumar Anbazhagan
William TranWilliam Tran
Try this:


AND(
Cleanup_Category__c = 1,
Owner.Profile.Name = "Inbound Sales",
Opportunity_Age__c >= 60,
Inactive_Override__c = FALSE,
OR(Text(Stagename) = "CLosed Won" ,
Text(Stagename) = "CLosed Lost")
)

Thx

All Answers

William TranWilliam Tran
Try this:


AND(
Cleanup_Category__c = 1,
Owner.Profile.Name = "Inbound Sales",
Opportunity_Age__c >= 60,
Inactive_Override__c = FALSE,
OR(Text(Stagename) = "CLosed Won" ,
Text(Stagename) = "CLosed Lost")
)

Thx
This was selected as the best answer
selvakumar Anbazhaganselvakumar Anbazhagan
Hi William tran, Thanks for your respond. Here I should exclude "closed won" or "closed lost". So can I use 60, Probability < 0.91, Probability > 0.01, Inactive_Override__c = FALSE ), AND( Cleanup_Category__c = 2, Owner.Profile.Name = "outbound Sales", Opportunity_Age__c > 90, Probability < 0.91, Probability > 0.01, Inactive_Override__c = FALSE ) ) So that I have used "OR" condition. Here am I used correct logical condition. ? Or i need to change any logic. If it is correct is it enough to change as below. OR(Text(Stagename) "CLosed Lost") Kindly share your suggestion. Thanks, Selva
William TranWilliam Tran
If the requirement is stage can not be closed then you should use OR(Text(Stagename) = "CLosed Won" ,Text(Stagename) = "CLosed Lost")
as I wrote above.  THe .91 probability does not make any sense unless your requirement say to use .91.

Does it not work?

Thx
selvakumar Anbazhaganselvakumar Anbazhagan
Thanks william. I will try it and let you know.
selvakumar Anbazhaganselvakumar Anbazhagan
Hi William Tran,

Thanks a lot. It works fine . Please confim me if i need add few more conditions can i use " OR" condition as shown below. I believe it give me good result. Just confirming that am using correct. Changes are shown as Bold.

OR(
AND(
Cleanup_Category__c = 1,
Owner.Profile.Name = "Inbound Sales",
Opportunity_Age__c >= 60,
Inactive_Override__c = FALSE,
OR(Text(Stagename) = "Closed Won" ,
Text(Stagename) = "Closed Lost")
),
AND(
Cleanup_Category__c = 1,
Owner.Profile.Name <> "Inbound Sales",
Opportunity_Age__c >= 120,
Inactive_Override__c = FALSE,
OR(Text(Stagename) = "Closed Won" ,
Text(Stagename) = "Closed Lost")
)
)
 
William TranWilliam Tran
SelvaKumar,

if you have additional restrictions/criteria, you can just add it in the AND like:
  
AND(
Cleanup_Category__c = 1,
ADDITIONALCRITERIA1__c = 5,
ADDITIONALCRITERIA2__c = 5,
Owner.Profile.Name = "Inbound Sales",
Opportunity_Age__c >= 60,
Inactive_Override__c = FALSE,
OR(Text(Stagename) = "CLosed Won" ,
Text(Stagename) = "CLosed Lost")
)
Your would use OR if you have a total different set of criterias which I don't think would apply to your case.

As a reminder, as a common practice, if your question is answered, please choose 1 best answer. 
But you can give every answer a thumb up if that answer is helpful to you. 

This will help keep the forum clean and help future users determine what answers are useful
and what answer was the best in resolving the user's issue. 

Please review your questions asked are marked them as appropriate.

Thanks