Fabian Varasteh

RollupConditionalSum in IfElse Statement

Discussion created by Fabian Varasteh on Apr 15, 2019
Latest reply on Apr 23, 2019 by 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

Outcomes