Duplicate Opportunities, trying to get the date with the max value

I've created a rule where i want to find the max value in an opportunity and use the date on that opportunity to update the Customer object Renewal date. However, on some accounts there are two opportunities that fit my filter criteria. I'm putting in the Showing Area - the Max value for the Opportunity and the Close Date. However, when i pull in the close date, it always uses the most recent close date when i go to assign the customer info objects vs. using the close date on the opportunity with the Max value. How can i get the Close date from the opportunity that has the max value if there is more than 1 opportunity that pulls in for my criteria?

11 replies

Userlevel 5
Not a solution, but a workaround. Try to put the opportunity max value as the first field in "Show fields" area. 

Let me know if this trick works.
Unfortunately that didn't work.

I get this to pull: 

MAX:Total_Opportunity_Value__c      CloseDate              Average_MRR__c

12829.65                                            10/5/2015 0:00           1069.13

2200                                                    11/25/2015 0:00       183.33

and the one that assigns is the 11/25/2015....

Userlevel 5
Aggregated field does not seem to come in right order, can you try putting Average MRR as the first field? (I assume this is not an aggregated field)
Thanks! I tried that, and unfortunately still got the smaller ARR and greater date. Anything else i can try?
Was informed the max/min does not work on date fields with rules (aggregation).   I too was looking for something similar but with another date field.   My workaround is to create a script in SFDC to run daily that checks the opps and grabs the MIN date from the list of open opps and store that MIN date on the account object.   Once that is complete, I will map to that account field within a Load rule.
Thanks Tom! Good to know for future rules
Hello Melissa,

I am the new Community Manager here at Gainsight and I am going through and checking to see if you were able to get this resolved or if it was an ongoing issue?


Userlevel 5
Badge +2
MIN/MAX Date fields in a SOQL query will perform the opposite action on all of the other fields. (i.e. MAX of Date, Min of ARR, Min of MRR, Min of Contract Renewal Date, etc.). Engineering/Product is working on getting this resolved. 
Thanks Steve!
Userlevel 5
This is possible with Bionic rules that came out with 5.5 release. Another thread on the same line