Expression question

[#1]

Hi guys,

My report got an Amount column which has the following  expression

if (Match(AccountType, 'Income'), Sum(AmountWithSign),

if (Match(AccountType, 'Cost of Goods Sold'), Sum(AmountWithSign),

if (Match(Transactions.GrsPrft, 'GrsPrft'), Sum(if (Match(AccountType, 'Income'), AmountWithSign, 0)) - Sum(if (Match(AccountType, 'Cost of Goods Sold'), AmountWithSign, 0)),

if (Match(AccountType, 'Expense'), Sum(AmountWithSign),

if (Match(AccountType, 'Other Income'), Sum(AmountWithSign),

if (Match(AccountType, 'Other Expense'), Sum(AmountWithSign),

if (Match(Transactions.IncExp, 'Net Ordinary Income'),

(Sum(if (Match(AccountType, 'Income'), AmountWithSign, 0)) - Sum(if (Match(AccountType, 'Cost of Goods Sold'), AmountWithSign, 0))) - (Sum(if (Match(AccountType, 'Expense'), AmountWithSign, 0))),

if (Match(Transactions.IncExp, 'Net Other Income'),Sum(if (Match(AccountType, 'Other Income'), AmountWithSign, 0))-Sum(if (Match(AccountType, 'Other Expense'), AmountWithSign, 0)),

if (Match(Transactions.NetIncome, 'Net Income'),

(((Sum(if (Match(AccountType, 'Income'), AmountWithSign, 0)) - Sum(if (Match(AccountType, 'Cost of Goods Sold'), AmountWithSign, 0))) - (Sum(if (Match(AccountType, 'Expense'), AmountWithSign, 0)))) +

(Sum(if (Match(AccountType, 'Other Income'), AmountWithSign, 0))-Sum(if (Match(AccountType, 'Other Expense'), AmountWithSign, 0)))),0)))))))))

If you look the green text....it says that "Net Ordinary Income" should be " (Income - COGS) - (Expense).

Now if you look at the picture above....you will find that Income and COGS are missing....they are missing because there is no transaction for Customer C1 which impacts Income and Expense account. So it is correct to not show Income and COGS amount in the report.

Here is the question....

I want Income and COGS to be treated as "0" for this calculation when they are missing for any customer....so the net impact would be

(0-0)-100 = -100....i.e. so Net Ordinary income and Net Income would be -100.

Can you please help me with it?

[Gysbert_Wassenaar]

Use the rangesum function instead of the - sign: rangesum(sum(A), -sum(B), -sum(C)). The rangesum function can deal with null values because it will simply sum all the non-null values.

[#3]

I changed the

if (Match(Transactions.IncExp, 'Net Ordinary Income'),

(Sum(if (Match(AccountType, 'Income'), AmountWithSign, 0)) - Sum(if (Match(AccountType, 'Cost of Goods Sold'), AmountWithSign, 0))) - (Sum(if (Match(AccountType, 'Expense'), AmountWithSign, 0)))

to

if (Match(Transactions.IncExp, 'Net Ordinary Income'),

rangesum(

(Sum(if (Match(AccountType, 'Income'), AmountWithSign, 0))),

- Sum(if (Match(AccountType, 'Cost of Goods Sold'), AmountWithSign, 0)),

- (Sum(if (Match(AccountType, 'Expense'), AmountWithSign, 0)))

),

but it is still not working.

Do you see any obvious mistakes here?