topic Sum function within an if condition in QlikView

Sum function within an if condition

apoorvasd — Tue, 13 Sep 2016 08:52:26 GMT

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.

Re: Sum function within an if condition

Anonymous — Tue, 13 Sep 2016 19:32:34 GMT

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

Re: Sum function within an if condition

sunny_talwar — Tue, 13 Sep 2016 19:50:12 GMT

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

Re: Sum function within an if condition

apoorvasd — Wed, 14 Sep 2016 05:54:33 GMT

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.

Re: Sum function within an if condition

apoorvasd — Wed, 14 Sep 2016 06:18:22 GMT

Hello,

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

Sorry

Re: Sum function within an if condition

Anonymous — Wed, 14 Sep 2016 17:05:20 GMT

Hi Apoorva,

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

Fernando V.

Re: Sum function within an if condition

apoorvasd — Thu, 15 Sep 2016 07:16:57 GMT

Hi Fernando,

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

Thank you.

Re: Sum function within an if condition

Anonymous — Thu, 15 Sep 2016 17:19:55 GMT

See the attached qvw

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

Hope this helps.

Fernando V.