ShowAll Questionssorted byDate Posted
selvakumar 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.

Best Answer chosen by selvakumar Anbazhagan
William 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

William 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 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 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 Anbazhagan
Thanks william. I will try it and let you know.
selvakumar 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 Tran
SelvaKumar,

if you have additional restrictions/criteria, you can just add it in the AND like:

```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")
)```
Your would use OR if you have a total different set of criterias which I don't think would apply to your case.