Expression question

Wed, 02 Apr 2014 18:59:46 GMT

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?

Re: Expression question

Gysbert_Wassenaar — Wed, 02 Apr 2014 19:33:26 GMT

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.

Re: Expression question

Wed, 02 Apr 2014 20:40:14 GMT

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)))

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?

topic Expression question in QlikView

Expression question

Re: Expression question

Re: Expression question