Nested If Condition issue

[#1]

Hi guys,

I have a nested If condition statement which is not working as expected...

Sum (if (Transactions.TxnType='Estimate' and Transactions.TxnType='Sales Order',0,

          if( (ClassifiedAccountType = 'Income'  or ClassifiedAccountType = 'Liability' or ClassifiedAccountType = 'Equity') and Transactions.DebitOrCredit = 'Credit',Transactions.Amount,

              if( (ClassifiedAccountType = 'Assest' or ClassifiedAccountType = 'Expense') and Transactions.DebitOrCredit = 'Debit', Transactions.Amount,

                  if( (ClassifiedAccountType = 'Income' or ClassifiedAccountType = 'Liability' or ClassifiedAccountType = 'Equity') and Transactions.DebitOrCredit = 'Debit',-Transactions.Amount,

                      if((ClassifiedAccountType = 'Assest' or ClassifiedAccountType = 'Expense') and Transactions.DebitOrCredit = 'Credit', -Transactions.Amount))))))

it suppose to work like this

if ((Transactions.TxnType='Estimate' and Transactions.TxnType='Sales Order) Then

          Amount = 0

else if ((ClassifiedAccountType = 'Income'  or ClassifiedAccountType = 'Liability' or ClassifiedAccountType = 'Equity') and Transactions.DebitOrCredit = 'Credit',) Then

          Amount = Transactions.Amount

else  if( (ClassifiedAccountType = 'Assest' or ClassifiedAccountType = 'Expense') and Transactions.DebitOrCredit = 'Debit') Then          

          Amount = Transactions.Amount

and so forth....

The problem is with the first If condition...

it is not making Amount = 0 when the TxnType is Estimate or Sales Order instead it is taking the second "If" because ClassifiedAccountType for Estimate and Sales Order is Income and Transactions.DebitOrCredit is Credit.

I assumed that once it has gone under first if condition then it will not under another "If" condition" So I kept it as first condition to exclude Estimate and Sales Order before i check ClassifiedAccountTypes.

Is there any way to fix it?

Saurabh

[#2 · Accepted solution]

Hi,

Would your first line contain a typing error, you're using "and" instead of "or":

TxnType = 'Estimate' AND TxnType = 'Sales Order' => this should be OR.

If this is just a typing error here and not in your app, then I'd suggest you use set analysis instead of nested if which can be resource consuming.

Another solution would be, if your conditions are a set of business rules that won't change, to create a field in your script, say AmountFinal, which will be filled with the amount that will be displayed; you'll use there your nested if as the definition of this field and then your expression would simply be sum(AmountFinal), for example:

Load TxnType,

ClassifiedAccountType,

DebitOrCredit,

if(Match(TxnType, 'Estimate', 'Sales Order'), 0, if(Match(ClassifiedAccountType, 'Income', 'Liability')... etc. ) as AmountFinal

Hope this helps.

[#3]

It certainly helps.

I had to use OR there...

I am going to use your last suggestion to have a AmountFinal column. It makes perfect sense.

Thank you.