How to use RollupConditionalSum to add multiple columns with criteria

Question

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")))))))))))))

Jeffrey White · Answer

Hi Jeffrey White , 
 I haven't tried this myself, but you could combine add and greaterThan to do what you need, something like: 
 add( ifElse(greaterthan(monthofyear(today()), 1), 0, rollupConditionalSum($fbsg_transactions_accounts,"january" ,"yr","2019"), ifElse(greaterthan(monthofyear(today()), 2), 0, rollupConditionalSum($fbsg_transactions_accounts,"february" ,"yr","2019"), ifElse(greaterthan(monthofyear(today()), 3), 0, rollupConditionalSum($fbsg_transactions_accounts,"march" ,"yr","2019"), ifElse(greaterthan(monthofyear(today()), 4), 0, rollupConditionalSum($fbsg_transactions_accounts,"april" ,"yr","2019"), ifElse(greaterthan(monthofyear(today()), 5), 0, rollupConditionalSum($fbsg_transactions_accounts,"may" ,"yr","2019"), ifElse(greaterthan(monthofyear(today()), 6), 0, rollupConditionalSum($fbsg_transactions_accounts,"june" ,"yr","2019"), ifElse(greaterthan(monthofyear(today()), 7), 0, rollupConditionalSum($fbsg_transactions_accounts,"july" ,"yr","2019"), ifElse(greaterthan(monthofyear(today()), 8), 0, rollupConditionalSum($fbsg_transactions_accounts,"august" ,"yr","2019"), ifElse(greaterthan(monthofyear(today()), 9), 0, rollupConditionalSum($fbsg_transactions_accounts,"september","yr","2019"), ifElse(greaterthan(monthofyear(today()), 10), 0, rollupConditionalSum($fbsg_transactions_accounts,"october" ,"yr","2019"), ifElse(greaterthan(monthofyear(today()), 11), 0, rollupConditionalSum($fbsg_transactions_accounts,"november" ,"yr","2019"), ifElse(greaterthan(monthofyear(today()), 12), 0, rollupConditionalSum($fbsg_transactions_accounts,"december" ,"yr","2019") ) 
 We are just adding the value if the month is before current month, but for previous year. 
 The only downside I see to these formula is that you have to update this every year. You could probably write a custom sugar logic to get the current year and use it instead. And, as you might know already - Sugar logic gets calculated only when the record is saved or updated, or viewed in the UI, so something to keep in mind, since reports might not exactly reflect the most accurate information.

Jeffrey White · Answer

I follow Neeraja P 
 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.