Thread Details
  • State Not Answered
  • Replies 16 replies
  • Subscribers 354 subscribers
  • Views 4516 views
  • Users 0 members are here
Forum Actions
Related

Modifying formula / adding dependency

Ada Napierala

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

    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

Reply
  • 0

    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

Children
No Data