How to use RollupConditionalSum to add multiple columns with criteria

For our Sugar instance I created a Transactions Module which holds the monthly volume by customer. The Module is setup per each Account in columns by month and year. This way we can look by customer at just 2019 or just 2020 volume or each months individual transaction volume. I am looking for a formula to calculate the Last Full Year to Date volume up to previous full month (Jan to Aug 2019 if currently in Sep 2020) from the Accounts Module (LFY to Date) Volume. I am providing the formulas I currently use as a guide. Any help is appreciated. 

I have a formula which calculates current YTD volume in Transactions Module:

add($january,$february,$march,$april,$may,$june,$july,$august,$september,$october,$november,$december)

This is the formula that calculates the current YTD volume in Accounts Module:

rollupConditionalSum($fbsg_transactions_accounts,"ytd_volume_c","yr","2020")

This is the formula that calculates the current LFY volume in Accounts Module:

rollupConditionalSum($fbsg_transactions_accounts,"ytd_volume_c","yr","2019")

I leverage this formula to calculate LFM volume from Accounts Module:

ifElse(equal(monthofyear(today()),1),rollupConditionalSum($fbsg_transactions_accounts,"december","yr","2019"),
ifElse(equal(monthofyear(today()),2),rollupConditionalSum($fbsg_transactions_accounts,"january","yr","2020"),
ifElse(equal(monthofyear(today()),3),rollupConditionalSum($fbsg_transactions_accounts,"february","yr","2020"),
ifElse(equal(monthofyear(today()),4),rollupConditionalSum($fbsg_transactions_accounts,"march","yr","2020"),
ifElse(equal(monthofyear(today()),5),rollupConditionalSum($fbsg_transactions_accounts,"april","yr","2020"),
ifElse(equal(monthofyear(today()),6),rollupConditionalSum($fbsg_transactions_accounts,"may","yr","2020"),
ifElse(equal(monthofyear(today()),7),rollupConditionalSum($fbsg_transactions_accounts,"june","yr","2020"),
ifElse(equal(monthofyear(today()),8),rollupConditionalSum($fbsg_transactions_accounts,"july","yr","2020"),
ifElse(equal(monthofyear(today()),9),rollupConditionalSum($fbsg_transactions_accounts,"august","yr","2020"),
ifElse(equal(monthofyear(today()),10),rollupConditionalSum($fbsg_transactions_accounts,"september","yr","2020"),
ifElse(equal(monthofyear(today()),11),rollupConditionalSum($fbsg_transactions_accounts,"october","yr","2020"),
ifElse(equal(monthofyear(today()),12),rollupConditionalSum($fbsg_transactions_accounts,"november","yr","2020"),
rollupConditionalSum($fbsg_transactions_accounts,"december","yr","2020"))))))))))))) 

Parents
  • I follow

    You must consider creating a custom sugarLogic formula for calculating current year and update your formulas accordingly. This way you won't need to worry about updating all formulas in a yearly base, and definitely your reports won't be that accurate.

    Had said that I would recommend creating an after_save logic hook which calculate all fields with respect to some Opportunity configuration (amount, expected_closed_date etc). That logic hook may not calculate in a synchronous base but in an assynchronous one through job queue in order to avoiding freezing record's view while saving it (User Experience in charge).

    This way all reports will fit the real scenario and users will not complain about performance degradation.

    André Lopes
    Lampada Global
    Skype: andre.lampada
  • I agree on having a more permanent solution created for our instance. The task to update the formula is currently only once per year so it's manageable (as long as we can get the formula working). We have two things going in our favor: 1) I update the volume each month via Excel import which does a recalculate on the rows. 2) Since Sugar added the 'Recalculate Values' function to all the tabs we can easily update any new rows by doing a 'Select All Records'. 

    Thanks again for the suggestion, I'm sure my team would appreciate the immediate update of values without my intervention. 

Reply
  • I agree on having a more permanent solution created for our instance. The task to update the formula is currently only once per year so it's manageable (as long as we can get the formula working). We have two things going in our favor: 1) I update the volume each month via Excel import which does a recalculate on the rows. 2) Since Sugar added the 'Recalculate Values' function to all the tabs we can easily update any new rows by doing a 'Select All Records'. 

    Thanks again for the suggestion, I'm sure my team would appreciate the immediate update of values without my intervention. 

Children
No Data