Question

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

melissa_allen
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

sundar
Rank
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.
melissa_allen
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....



sundar
Rank
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)
melissa_allen
Thanks! I tried that, and unfortunately still got the smaller ARR and greater date. Anything else i can try?
tom_wix
Badge
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.
melissa_allen
Thanks Tom! Good to know for future rules
evan_luberda
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?



Thanks,

-Evan
steve_davis
Rank
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. 
melissa_allen
Thanks Steve!
sundar
Rank
Userlevel 5
This is possible with Bionic rules that came out with 5.5 release. Another thread on the same line
melissa_allen
Thanks!

Reply


Terms & ConditionsCookie settings