Modifying formula / adding dependency

Ada Napierala,

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

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.