Details about this thread
  • State Suggested Answer
  • +1 person also asked this people also asked this
  • Replies 10 replies
  • Answers 1 answer
  • Subscribers 322 subscribers
  • Views 550 views
  • Users 0 members are here
Related content from around SugarClub

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

Steffen Scheid

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, “date_closed”, createList(“open_rli”), createList(“true”)).

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

Parents Reply
  • 0 in reply to Vincent Amari

    Hi  Thank you very much for looking into my question. 

    The objective is to evaluate opportunities in a dashlet and report those with expired expected close dates, indicating a need for action.  Regrettably, the function

    rollupConditionalMinDate($revenueLineItems, “date_closed”, createList(“open_rli”), createList(“true”)).

    does not operate as intended; both true and false cases are displayed. For instance, revenue line items with sales stages closed both won and closed lost.

    The sales stage close won is included in the forecast stage, so unfortunately that won't work. 

    All the best
    Steffen

Children
  • 0 in reply to Steffen Scheid

    Hi  
    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.

    .

    CRM Business Consultant

  • 0 Sugar Employee Badge in reply to Steffen Scheid

    Hello  , 

    I tried your approach locally and made a small adjustment to the naming in the first formula to ensure the checkbox is set in some test scenarios.

    For the second formula, I realized it might not be working as expected due to how it’s currently set up. Could you please try the formula below instead? for the field earliest_close_date_c


    rollupConditionalMinDate($revenuelineitems,"date_closed",createList("open_rli_c"),createList("1"))

    As you can see from my local testing, this version takes the open_rli_c checkbox into account and removes the expected close date from the RLI as intended.





    Let me know if this helps.

    Cheers,

    André