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!

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

Children
  • 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.