function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Ivan WinzerIvan Winzer 

Calculate Last opportunity Won

So i have found a working solution thru the sucess community but still having a issue with the outcome. So below is what i have created in our org:

First- 
Create a formula field on the Opportunity
Name this something like "CloseDatewithAmount"
Formula field will be a number field with 2 decimals
Use the following formula:
VALUE(text(YEAR(CloseDate)) & 
  IF(LEN(text(MONTH(CloseDate))) = 1, "0" & TEXT(MONTH(CloseDate)),         TEXT(MONTH(CloseDate))) 
   & IF(LEN(text(DAY(CloseDate))) = 1, "0" & TEXT(DAY(CloseDate)), TEXT(DAY(CloseDate)))
      &  TEXT(Amount))

Second-
Create a Roll-up Summary field on the Account
Name this field something like "LastClosedWonFormula"
Summarized object = Opportunity
Choose Max as the rollup type
Field to aggregate = CloseDatewithAmount  (or whatever you named your Opportunity formula field)
Under filter criteria choose - Only records meeting certain criteria should be included in the calculation
For the criteria choose Won equals True

Third-
Create a formula field on the Account
Name this field something like "Last Closed Won Opportunity Amount"
Make the field a currency field with 2 decimals
The formula is : 
VALUE(TRIM(MID(TEXT(LastClosedWonFormula__c), 9, 25)))

So all of this has worked and on each opp i see the data. So where my issue is, that the roll up created to pull the most recent date and amount its pulling the largest amount which is not technically the last purchase. For example:

Customer Abby has a purchase on 10/31/2012 for $107 and another on 07/05/2013 for $50. So the data on each record reads as
2013070550
20121031107

Which it should but the problem is that the max rollup of the field created in the first section is pulling in 20121331107 since it is the highest amount in the list however it should be pulling in 2013070550 since 2013 is more than 2012.

So hopefully someone can help me figure out what is wrong with my rollup summary field or the fields in general to get it to pull by the year and not the enritre amount in regards to sorting the data to use.

All help is greatly appreciated :)

Ivan
 
Alain CabonAlain Cabon
Hello Ivan,

First- 
Create a formula field on the Opportunity
Name this something like "CloseDatewithAmount"
Formula field will be a number field with 2 decimals
Use the following formula:
VALUE(text(YEAR(CloseDate)) & 
  IF(LEN(text(MONTH(CloseDate))) = 1, "0" & TEXT(MONTH(CloseDate)),         TEXT(MONTH(CloseDate))) 
   & IF(LEN(text(DAY(CloseDate))) = 1, "0" & TEXT(DAY(CloseDate)), TEXT(DAY(CloseDate)))
      &  LPAD(TEXT( Amount ), 10, '0')

if full amount, otherwise: LPAD(TEXT(ROUND(test_number__c,0) ), 10, '0') if decimal point.

Regards
Alain