Details about this thread
  • State Suggested Answer
  • Replies 5 replies
  • Answers 2 answers
  • Subscribers 311 subscribers
  • Views 2822 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"))))))))))))) 

Parents
  • 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.

Reply
  • 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.

Children
  • 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.