Custom Formula help

[Shazza]

I need to create an Account Field, that types the various accounts we have.  I have a field that shows the date of the last closed won opp.  I need to create another field that if this closed won opp is within 12 months the type of account is shown as Existing, if the opp is closed won with a date greater than 12 months then dormant is returned.  If there are no opps closed won then Suspect.  Can anyone give me any pointers please?

[Stevemo]

This is a custom formula field that I created on our Org.  It is used to set visual indicators that reflect the current status of the Account based on the recency of Sales Activity (by evaluating custom Opportunity Summary Roll-Up fields).  You can easily hack this and change the Image Link references to be values in your custom Account Type field.

IF(AND(Active__c = FALSE) , IMAGE( "/servlet/servlet.FileDownload?file=01540000000Ks9o", "Grey:Inactive"), IF (AND(Current_FY_Revenue__c > 0) , IMAGE( "/servlet/servlet.FileDownload?file=01540000000Ks9V", "Green:Active Client"), IF (AND(Opportunity_Count_Open__c > 0) , IMAGE( "/servlet/servlet.FileDownload?file=01540000000Ks9t", "Blue:Active Prospect"), IF (AND(Opportunity_Count_Open__c < 1) , IMAGE( "/servlet/servlet.FileDownload?file=01540000000Ks9e", "Yellow:Inactive"),null))))

 

[Shazza]

Hi Stevemo

I seem to be struggling with this.  I have a roll up custom field that tells me the date of a last closed won opp for an account.  If this opp closed within 365 days from todays date then I want a new custom field on the account page to say "existing customer".  If the opp closed more than 365 days ago then I want it to say "dormant customer".  If there is no opp at all then I would want it to say "Suspect Customer". 

I've tried hacking the code you sent over but keep getting syntax errors.  I've tried to create a custom field, text, and also numbers but keep getting it wrong.  Do I have to create anything else before I can create this custom field?

[Stevemo]

Hi Shazza,

Can you post the source code that you are using to evaluate your Opportunity Close Date, and calculate your Customer Type?

[Shazza]

Hi Stevemo

My roll up field is a

 date field.  Could that be the problem.  I'm trying to create a custom field, text,

IF(Last_Closed_Won_Opp_Date__c  < 365 days = "Dormant Customer") IF(Last_Closed_Won_Opp_Date__c >365 days = "Existing Customer") IF (Last_Closed_Won_Opp_Date__c = Null = "Suspect Customer)

[jhamlet]

I'm wondering if anyone has gotten this to work successfully.  I too am trying to do a roll up based on the last 12 months / 365 days, but can't seem to find a way to make this work in a formula.

If <date> is >= 365 days (or 12 months), then ...

does anyone have a creative solution to this issue to return a value for a trailing 12 month relative date range?

[Stevemo · Accepted solution]

Are you guys looking for something like this?

IF( Max_Win_Date__c < TODAY() - 365, "Dormant Customer",
IF(Max_Win_Date__c > TODAY() -365, "Existing Customer",
"Suspect Customer"))

 ***  Max_Win_Date__c is a Roll-Up Summary Field that displays the Close Date of MAX dated Closed/Won Opportunity 

Message Edited by Stevemo on 02-08-2010 02:26 PM

[jhamlet]

Thanks Stevemo.  this is looks like it is just what we were looking for.  Clever

[Stevemo]

No problem J, that's why they pay me the BIG Bucks!

http://www.youtube.com/watch?v=VtVFcJiqHSQ