Tuesday, May 7, 2019

Get approximate monthly Projected Total on Opportunities

Consider the following scenario:

1. User has custom Opportunity fields for Start Date and End Date
2. User wants to get approximate monthly total between the elapsed dates
3. User is not using calendar months in this instance but rather getting monthly totals for each opportunity, where number of months is defined as: number of days/30

To get the approximate monthly Projected Total, use the following formula on the Results tab > Columns subtab of the saved search:

Formula (Numeric): {projectedamount}/nullif((round(({custbody2}-{custbody1})/30)),0)

custbody2 = End Date field ID
custbody1 = Start Date field ID

  • The formula above gets the total number of days between End and Start date.
  • This is then divided by 30 (the accepted number to get approx. number of months).
  • This is then rounded up since the assumed requirement is just number of months.
  • The final value of the denominator then will divide the projected total.

Another use of the formula will be to substitute the custom fields with the available fields in the Opportunity record where:

a. custbody2 is substituted by {closedate}, which is the actual closure of the opportunity
b. custbody1 is substituted by {trandate}, which is the date the opportunity record is created but can be edited via: Edit Opportunity record > System Information > Date Created field

No comments:

Post a Comment