Details about this thread
  • State Suggested Answer
  • Replies 6 replies
  • Answers 1 answer
  • Subscribers 326 subscribers
  • Views 247 views
  • Users 0 members are here
Related content from around SugarClub

"Earliest date to close" shows incorrect results.

Steffen Scheid

Hello,

We have a date field in Opportunities called “Earliest date to close” which is calculated using the following formula from the Expected Close dates of the associated Revenue line items. 




Revenue items with the sales stages “closed won,” “closed lost”,"won_in_execution","won_completed", and “postponed indefinitely” should not be taken into account, so I have created two additional fields in the revenue line items.

arc_eligible_expected_close
Type: Date (Calculated = Yes)



I also created a field null_date_c
Type: Date, (not calculated, default = none).

This formula gives the right values.






Unfortunately, these are not transferred to the earliest close date field of the opportunity.


Where is the error?
Help and tips are greatly appreciated!


Thanks
Steffen

  • 0 Sugar Employee Badge

    Hi Steffen,

    Your reasoning makes sense, but keep in mind that using maxRelatedDate() will give you the latest closing date (furthest out in the future), not the earliest.

    For your scenario, a better out-of-the-box option is rollupConditionalMinDate(), and the good thing is that allows conditional filtering without the need for creating a helper field.

    From what you describe this formula should do it: 

    rollupConditionalMinDate(
    $revenuelineitems,
    "date_closed",
    createList("sales_stage"),
    createList(createList("Prospecting","Qualification","Needs Analysis","Value Proposition"))
    )

    You will need to add or replace the values on the second list with the values that you want to include on your filter.

    Let me know if this works for you. 

    Cheers, 

    André 

  • 0 in reply to Andre Serrazina

    Hi  That's a good point. I wanted to investigate whether there is also a minRelatedDate() formula. But your suggestion is even simpler and it works! Thank you very much!

    Cheers,
    Steffen

  • 0 in reply to Andre Serrazina

    Hi  - Thanks again for the advice.

    We have implemented this, but unfortunately the function does not seem to work for certain sales stages (e.g. in_contact and Proposal / Price Quote). Do you have any idea why this is the case? We have checked this in various Sugar instances.

    Cheers,
    Steffen

  • 0 Sugar Employee Badge in reply to Steffen Scheid

    Hello  , 

    Thanks for sharing this. 


    If it doesn't work for certain Sales Stages, I would start by double checking if the values that are being inserted in the formula are exactly the same as you have defined in the Item name in the Dropdown sales_stage_dom

    For Example, you've wrote that the stage is "Proposal / Price Quote" and that has a couple of more spaces comparing to the value 'Proposal/Price Quote' that is defined by default in the dropdown Editor:






    If everything is correctly set up here, I’d recommend opening a support case so they can check if there’s anything else at play.

    Let me know if this helps.

    Cheers,

    André

  • 0 in reply to Andre Serrazina

    Hi  ,

    Thank you for your prompt reply. Yes,I also suspected that the field names did not match, but they do.
    Formula:

    rollupConditionalMinDate(
    $revenuelineitems,
    "date_closed",
    createList("sales_stage"),
    createList("new","on_hold","Prospecting","in_contact","Proposal / Price Quote","Demo Presentation / Test Shoot","in_process","Negotiation / Review"))




    I will create a support case.

    Kind regards,
    Steffen

  • 0 in reply to Steffen Scheid

    Hi  

    Just for your information: I tested the function in the Sugar demo version using the original sales stages ("Qualification","Prospecting","Needs Analysis","Value Proposition","Id. Decision Makers","Perception Analysis","Proposal/Price Quote","Negotiation/Review")

    However, the function only works for the first sales phase in the list.

    rollupConditionalMinDate(
    $revenuelineitems,
    "date_closed",
    createList("sales_stage"),
    createList("Qualification","Prospecting","Needs Analysis","Value Proposition","Id. Decision Makers","Perception Analysis","Proposal/Price Quote","Negotiation/Review"))


    I will create a support case.

    Cheers,
    Steffen