RollupConditionalSum in IfElse Statement

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 Reply Children
No Data