Earliest expected closing date for an opportunity involving multiple revenue line items.

Question

Hi there, Following several unsuccessful attempts, I have finally found a solution that enables me to identify the earliest expected closing date for an opportunity involving multiple revenue line items. 
 To achieve this, I created a calculated checkbox field "open_rli" on the revenue line item and defined the sales stages for which the RLI should still be included in the calculation. 
 isInList($sales_stage, createList("new", "on_hold", "Prospecting", "in_contact", "Proposal / Price Quote", "Demo Presentation / Test Shoot", "in_process", "Negotiation / Review")) 
 In the Opportunities, I entered the formula for the "earliest_close_date" field. 
 rollupConditionalMinDate($revenueLineItems, &ldquo;date_closed&rdquo;, createList(&ldquo;open_rli&rdquo;), createList(&ldquo;true&rdquo;)). 
 This basically works, except in cases where 'open_rli' = false, in which case this date should not be taken into account. Unfortunately, this does not work; both true and false cases are displayed. Does anyone have any ideas? Best regards, Steffen

Steffen Scheid · Answer

Hi Steffen Scheid I see your issue, and it is a shame the default calculation for Opportunity Expected Close date doesn't already do this as it would make sense, and the formula is hidden in code so not amendable without code customisation. However, as your requirement is to create a report to identify Opportunities with RLI's that have oldest or expired expected close dates, can I suggest a simpler approach to create the report based on RLI's instead? The report can display both the RLI's and the related Opportunity. Hope that helps.