Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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.