Skip to main content
Topics#FormulasDomanda di Ivan Winzer II
Ivan Winzer II ha fatto una domanda in #Formulas
8 dic 2016, 19:58

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

 
1 risposta
  1. Alain Cabon (Air France)
    15 dic 2016, 00:31
    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
Scrivi una risposta...
0/9000