Rollup of RLI likely field (sales stage = closed won)

Hi Jaymie Raymond,

Since you only want to total RLIs that are in Closed Won, you should be able to use the following formula:

rollupConditionalSum($revenuelineitems,"likely_case","sales_stage",createList("Closed Won"))

This would give you a total in your system default currency. If you ever wanted to include additional sales stages, you would modify the createList to account for those values:

createList("Closed Won", "Pricing Negotiation", ...)

Kind regards,

Chris

Hi Chris Raffle

Can I also filter these further? I would like to filter it by creation date, specifically for this calendar year.

As always thanks again for your help!

Jaymie

Hi Jaymie Raymond,

You cannot configure a conditional rollup calculation on multiple fields and you also cannot do conditional rollups based on date ranges. 

What I would recommend instead is that you create a custom text field on the RLI module that does the following:

1. Checks if the status is 'Closed Won'
2. If status is valid, then it stores the year from the record's date created

The formula would look like this:

ifElse(equal($sales_stage,"Closed Won"),year(date(toString($date_entered))),"")

Once you create this field & formula, you would need to trigger a 'recalculate values' action from the RLI list view for any records created this year so that the proper value is stored.

Then, the rollupConditionalSum formula on the Accounts module would change from looking at the Sales Stage field to your new custom field and the createList formula inside that would be set to whatever year(s) you want to rollup:

rollupConditionalSum($revenuelineitems,"likely_case","custom_field_you_created_c",createList("2022"))

Hi Jaymie Raymond,

You cannot configure a conditional rollup calculation on multiple fields and you also cannot do conditional rollups based on date ranges. 

What I would recommend instead is that you create a custom text field on the RLI module that does the following:

1. Checks if the status is 'Closed Won'
2. If status is valid, then it stores the year from the record's date created

The formula would look like this:

ifElse(equal($sales_stage,"Closed Won"),year(date(toString($date_entered))),"")

Once you create this field & formula, you would need to trigger a 'recalculate values' action from the RLI list view for any records created this year so that the proper value is stored.

Then, the rollupConditionalSum formula on the Accounts module would change from looking at the Sales Stage field to your new custom field and the createList formula inside that would be set to whatever year(s) you want to rollup:

rollupConditionalSum($revenuelineitems,"likely_case","custom_field_you_created_c",createList("2022"))

I updated the above post with a cleaner solution for the custom field on the RLI module.