Details about this thread
  • State Suggested Answer
  • Replies 2 replies
  • Answers 1 answer
  • Subscribers 326 subscribers
  • Views 60 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

Parents
  • 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é 

Reply
  • 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é 

Children