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!

  • ,

    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 Ada,

    Not sure below is helpful, because I'm not sure that I understand the business model of your Sugar, in particular
    - whether the regular Sugar renewals model is employed - there is a Renewal checkbox in RLI that Indicates whether this line item is a renewal,
    - what do the custom fields in your formula stand for, and which module do they belong
    - why Opp types represent factually the type of different RLIs (Upsell, Renewal, ProfService, ect) that are united under the umbrella of the Opp efforts
    - whether the purpose is to calculate ARR for an Opp or rather for an Account

    but if the question would be

    how to calculate Opp.ARR for the listed types of Opps by rolling up sum RLIs' Likely fields for all RLIs of the Opp (no exclusions in rollup summation based on some RLI type or any other criterion), then the formula may look like this:

    ifElse(isInList($opportunity_type,createList("New Business","Upsell","Renewal with Upsell")),rollupSum($revenuelineitems,"likely_case"),0)

    Perhaps, if I had more info regarding the data model, any suggestions regarding calculations would have been more helpful

    Best Regards,
    Dmytro Chupylka

    integroscrm.com
    We make work in Sugar CRM system faster, more convenient and efficient

  • 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? 

  • 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?