Details about this thread
  • State Not Answered
  • Replies 3 replies
  • Subscribers 309 subscribers
  • Views 2436 views
  • Users 0 members are here
Related content from around SugarClub

RollupConditionalSum in IfElse Statement

Fabian Varasteh

Hi,

our company is dealing mostly with yearly subscription customers, but some of them are on multi-year and half-year contracts.

I wanted to display the current annualized invoice revenue on the associated account record and came up with the following formula on a custom decimal field type.

However, my formula doesn't seem to be stable and displays wrong amounts for some account records.

ifElse(
   equal(related($members,"cover_period_c"),"Half-Year Customer"),
   rollupConditionalSum($invoice_account_1,"total_recurring_aud_c","financial_year_c","2020"),
   ifElse(
      and(
         equal(related($members,"cover_period_c"),"Multi-Year Customer"),
         not(equal(number(related($members,"next_renewal_fy_c")),"2020")),
         not(greaterThan(abs(rollupConditionalSum($invoice_account_1,"annualized_revenue_aud_c",

         "financial_year_c","2020")),0))
      ),
      related($members,"fy19_recurring_revenue_aud_c",
      rollupConditionalSum($invoice_account_1,"annualized_revenue_aud_c","financial_year_c","2020")

)

The formula is supposed to display the following for the Financial Year 2020:

AccountDisplay
Half-Year CustomerFY2020 total invoice rollup in AUD
Multi-Year Customer not expected to be invoiced this FY (Next-Renewal <> 2020)previous FY annualized invoice amount (here FY19)
1-Year Customer & MY customers being invoiced this FYFY2020 annualized invoice rollup in AUD
Parents
  • 0

    Thought I provide a quick explanation of what I mean with displaying incorrectly.

    Whenever I update an account record, the "FY20 Recurring Revenue AUD" displays the correct value, in this case related($members,"fy19_recurring_revenue_aud_c").

    However, once I check another account record it displays exactly the same as the value on the most recent unrelated account record.

    After updating this account record it then overwrites the previously updated account record again.

    Example:

    Account 1                                                                                    Account 2

Reply
  • 0

    Thought I provide a quick explanation of what I mean with displaying incorrectly.

    Whenever I update an account record, the "FY20 Recurring Revenue AUD" displays the correct value, in this case related($members,"fy19_recurring_revenue_aud_c").

    However, once I check another account record it displays exactly the same as the value on the most recent unrelated account record.

    After updating this account record it then overwrites the previously updated account record again.

    Example:

    Account 1                                                                                    Account 2

Children
No Data