Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
apoorvasd
Creator II
Creator II

Sum function within an if condition

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

 

ABAB
Account_Number5007100500810050071005007200Report
4101100 (other Account_Number)5005001000
4300910500500500

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.

7 Replies
Anonymous
Not applicable

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

sunny_talwar

If what fernando.vizcaino‌ doesn't work for you, would you be able to provide a sample to look at?

apoorvasd
Creator II
Creator II
Author

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.

apoorvasd
Creator II
Creator II
Author

Hello,

SAP database is the source system for this. Data are confidential, so i don think i can upload a sample qvw.

Sorry

Anonymous
Not applicable

Hi Apoorva,

Would you be able to create an example of the data structure? So I can examine what went wrong.

Fernando V.

apoorvasd
Creator II
Creator II
Author

Hi Fernando,

I have attached an image which explains the logic in detail.

Thank you.Sum of Amount Logic.PNG

Anonymous
Not applicable

See the attached qvw

I've altered some data compared to your example in Debit / Credit field for testing.

Hope this helps.

Fernando V.