Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a field called "Account_Number" and two other fields say "A" and "B". Now the requirement is, I need to calculate amount for the Account numbers. Condition for this is, when the Account_Number= 4300910 , 4300920, 9000405 then, it should check if first four digits of A and B are not equal, if so then only calculate the amount for these account numbers. Whenever first four digits of A and B are equal, we have to ignore that amount. For rest of the Account_Numbers, amount should be calculated without applying the condition for A and
A | B | A | B | ||
Account_Number | 5007100 | 5008100 | 5007100 | 5007200 | Report |
4101100 (other Account_Number) | 500 | 500 | 1000 | ||
4300910 | 500 | 500 | 500 |
I tried the below expression, but not able to get it work.
=if(match(Accounts_Number,'4300910' ,'4300920','9000405'),if(left(A,4)<>left(B,4),sum(Amount)),sum(Amount))
Note: "Amount" is again an expression provided at the script side (an "if" condition)
Any help on this?
Thank you.
Hi Apoorva,
Try this.
=sum(aggr(if(match(Accounts_Number,'4300910' ,'4300920','9000405'),if(left(A,4)<>left(B,4),sum(Amount),0),sum(Amount)),Accounts_Number,A,B))
If what fernando.vizcaino doesn't work for you, would you be able to provide a sample to look at?
Hi Fernando,
Thanks for the suggestion.
I tried the expression but unfortunately i am not able to see the desired output for Amount!
Anymore suggestions?
Thank you.
Hello,
SAP database is the source system for this. Data are confidential, so i don think i can upload a sample qvw.
Sorry
Hi Apoorva,
Would you be able to create an example of the data structure? So I can examine what went wrong.
Fernando V.
Hi Fernando,
I have attached an image which explains the logic in detail.
Thank you.
See the attached qvw
I've altered some data compared to your example in Debit / Credit field for testing.
Hope this helps.
Fernando V.