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

  • Hi Chris, 

    Yes you are absolutely right - thank you very much for your help, that's a big milestone for us. 

    I have just one follow up question regarding discounts. 

    Right now, I can see that there is a field to insert discount in percentage. Is there another standard field to put discount as currency that is already pre-build it Sugar? I cannot seem to find it?

  • Hi ,

    The field where you can enter a discount percentage also supports putting in flat currency amounts as well. Click the percent sign to toggle between the two options:

  • Hi Chris, 

    Thanks yes I was able to figure it out, that's very useful.

    I began to move the data from my old fields to new ones and I stumbled upon a conversion (?) problem. 

    When inputting Unit Price, Service Duration (e.g. 24 months - I replaced service duration with Quantity to calculate Calculated Amount) in USD (it's my base currency) I receive correct results. 

    However, when I input e.g. Unit Price 200,000 EUR and Service Duration 36months, my calculated amount is 584,000 EUR instead of 600,000 EUR. 

    Are there any steps of fields I need to use for the conversion?

  • Hi Ada,

    Please clarify a few things for me to better understand the issue:

    1. What is the current formula you are using for the total_amount field on the RLI module?
    2. What currency is flagged as your system default currency under Admin > Currencies?
  • Hi Chris, 

    This the formula: 

    ifElse(and(isNumeric($quantity), isNumeric($discount_price)),
    ifElse(equal($quantity, 0),
    $total_amount,
    currencySubtract(
    ifElse(isNumeric($subtotal), $subtotal, multiply($discount_price, $quantity)),
    ifElse(equal($discount_select, "1"),
    currencyMultiply(
    ifElse(isNumeric($subtotal), $subtotal, multiply($discount_price, $quantity)),
    currencyDivide($discount_amount, 100)
    ),
    ifElse(greaterThan($quantity, 0),
    ifElse(isNumeric(toString($discount_amount)),
    ifElse(greaterThan(0, $discount_price),
    negate($discount_amount), $discount_amount
    ), 0
    ),
    ifElse(isNumeric(toString($discount_amount)), negate($discount_amount), 0))
    )
    )
    ), ""
    )

  • Hi Ada,

    I may be misunderstanding something then from your prior post. I took your post to mean that you getting the Calculated Amount (total_amount) value by doing the following:

    1. Figuring out the per month price of the Unit Cost (e.g. $200,000 / 12)
    2. Multiplying that per month price by the service duration

    However, the formula you shared for total_amount appears to be the default formula. Do you have another field performing the calculation where it is showing the 584k Euro amount? If so, what formula are you using there?

  • Hi Chris, 

    This is everything I am using to get NewARR: 

    1. Unit Price

    ifElse(
    and(
    equal($product_template_id, ""),
    not(isNumeric($discount_price)),
    not(equal($quantity, 0))
    ),
    divide($likely_case, $quantity),
    $discount_price
    )

    2. Service (tick box), Subscription Start Date, Service Duration 

    --> QQ on this: is it possible to change Service Duration by default to 12 months?

    3. Calculated Amount

    ifElse(and(isNumeric($quantity), isNumeric($discount_price)),
    ifElse(equal($quantity, 0),
    $total_amount,
    currencySubtract(
    ifElse(isNumeric($subtotal), $subtotal, multiply($discount_price, $quantity)),
    ifElse(equal($discount_select, "1"),
    currencyMultiply(
    ifElse(isNumeric($subtotal), $subtotal, multiply($discount_price, $quantity)),
    currencyDivide($discount_amount, 100)
    ),
    ifElse(greaterThan($quantity, 0),
    ifElse(isNumeric(toString($discount_amount)),
    ifElse(greaterThan(0, $discount_price),
    negate($discount_amount), $discount_amount
    ), 0
    ),
    ifElse(isNumeric(toString($discount_amount)), negate($discount_amount), 0))
    )
    )
    ), ""
    )

    4. NewARR

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

    I am testing this once more to see the currencies.

  • I was always wondering why writing no-code formulas is sometimes a topic starter in the developers' branch of the Club - DevClub - also I found people sometimes call writing formulas for Cal Fields "coding a formula".

    Meeting the formula like above makes it clearer why - we can imagine how much time and effort it takes to write and validate implementation -  I do admire the great persistency of Sugar Admins in getting such monsters to work!

    As for me, this is clearly the case that shows what trying to put more-or-less complex calculation logic into the single Calc Field may lead to - IMHO, even if to be lucky enough to configure "superformula" once, the Prefix (Polish) Notation of the Calc Fields may turn the result into hardly readable and non-easy understandable ...code?.

    I know people that were facing the same challenge of implementing calculation logic for Sugar and they never regret employing other no-code techniques for setting up automation in Sugar - the OOTB SugarBPM to visualize workflows automation, and Logic Builder service to regularly draw and redraw calculations, set up and adjust algorithms implementations

    I hope this makes sense

    Best Regards,
    Dmytro Chupylka

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



  • To be honest, I am quite surprise on how SugarCRM approached calculated fields and formulas - almost as low-coding.

    Also, I noticed that some out-of-the-box fields from SugarCRM are low-coded.

    My experience with other CRM systems is much different, where a formula is a formula and has nothing to do with coding.

    Anyway, I hope I am good for now with calculated fields :D