Details about this thread
  • State Suggested Answer
  • Replies 5 replies
  • Answers 2 answers
  • Subscribers 308 subscribers
  • Views 2708 views
  • Users 0 members are here
Related content from around SugarClub

How to use RollupConditionalSum to add multiple columns with criteria

Jeffrey White

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

  • 0

    Hi ,

    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.

  • 0

    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
  • 0 in reply to Neeraja P

    I appreciate you taking the time to create this formula. In attempting to insert as written I received the following error ("no close parentheses")

    I added the number of ) to account for all the rows and then received ("greaterthan: no such function defined").

    I modified to greaterThan and received this error ("ifElse: Requires exactly 3 parameters")

    The logic seems fine, but I am unable to get past this error while keeping the formula intact. I realize without having my module the task of creating a formula is much more difficult so I appreciate you taking the time to attempt. If you have any suggestions on a fix for the ifElse logic I will attempt. Thanks again.  

  • 0 in reply to André Lopes

    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. 

  • 0 in reply to Jeffrey White

    Hi , my bad, I didn't check the syntax before posting, but here is a corrected version, can you please try this out:

    Added a ")" at the end of every ifElse, which was missing - and changed "greaterthan" to "greaterThan":

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