Modifying formula / adding dependency

Hello,

I added a formula to one of our custom fields that calculates any new ARR on an opportunity and revenue line item (especially usefully for renewals with upsells):

ifElse(contains($arr_yes_no_c,"Y"),subtract($discount_price,$renewal_arr_c),"")

However I would like to add one more condition (or exception) to this formula. In case of Renewals (NOT Renewals with upsells) that have a contraction (Year 1 ARR = 10,000 USD / Year 2 ARR = 5,000 USD) I would like the new ARR to be 0 - right now it's negative. 

So is there a way to modify this formula so that it excludes Renewals (Renewals are chosen from another custom field - Opportunity type) or is there a dependency that I can add that will say only calculate New ARR for New Business, Upsells and Renewals with Upsell types?

Thank you!

Parents
  • ,

    If this scenario is the only scenario in which the ARR would be negative, you can modify the formula to account for that scenario:

    ifElse(contains($arr_yes_no_c,"Y"),ifElse(greaterThan(subtract($discount_price,$renewal_arr_c),0),subtract($discount_price,$renewal_arr_c),"0"),"")

  • Hi Chris, 

    I tried that, however it's not returning any values now for New ARR. 

    I was wondering - can I add to my formula a condition that will exclude Opportunity type - renewal?:

    ifElse(contains($arr_yes_no_c,"Y"),subtract($discount_price,$renewal_arr_c),"") - would that be the rollupConditionalSum function? How can I exclude (rather than include values?) 

    For my Opportunity type I have: New Business, Upsell, Renewal with Upsell, Renewal, Additional Services - I would like to exclude Renewal and Additional Services

    Thanks for your help!

  • Hi ,

    That's odd that the formula is not returning results for you. I created the same fields on the Revenue Line Items module and confirmed that the calculation works as expected:

    If you would prefer to just use the rollupCoditionalSum formula at the opportunity level, I recommend the following steps:

    1. Create a currency field (e.g. new_arr_c) on the RLI module that calculates the potential new ARR with your given calculation:
      subtract($discount_price,$renewal_arr_c)
    2. Create a text field (e.g. count_arr_c) on the RLI module to flag whether it should be counted in the opportunity calculation
    3. Set a formula in the field to flag it when one of the desired types is set on the RLI record:
      ifElse(isInList($product_type,createList("New Business","Upsell","Renewal with Upsell")),"Yes","No")
    4. On the Opportunity module create a currency field (if you already haven't) with the following formula to only pull in the values from the desired RLI types:
      rollupConditionalSum($revenuelineitems,"new_arr_c", "count_arr_c", "Yes")

    Once you add the fields, you will need to recalculate values on any existing RLIs to properly report up the information to their corresponding opportunities. 

  • Hi Chris, 

    Interesting results - maybe we are not fully aligned. Is it possible that my results for NEW ARR are 0 because Type (e.g. New Business) I have this field on the Opportunity Layout, not on the RLI layout? 

Reply Children
  • Hi ,

    I made the following assumptions in the solution I provided above:

    • The formula subtract($discount_price,$renewal_arr_c) is being calculated at the RLI level because $discount_price is not a field on the Opportunity module
    • While there is a Type field at the opportunity level (opportunity_type), there is also a stock type field at the RLI level (product_type). product_type initially inherits the value set for opportunity_type when creating a new RLI. The product_type does not change values if the opportunity_type is changed after the RLI is created. I assumed there were scenarios where, in an opportunity with multiple RLIs, some RLIs would be classified as upsells while other RLIs could be a reduction in revenue, so the formula to flag whether an RLI should roll up to your New ARR calculation is necessary. 

    Are these accurate assumptions?