topic Re: Facing issue in Aggr() in Qv12 in QlikView

Facing issue in Aggr() in Qv12

Anonymous — Wed, 30 Nov 2016 13:30:53 GMT

In a Chart i am showing Transaction ID wise sum of Statement Amount

Case 1 : Transaction ID = 6083

Transaction Id	Value Date	Posting Date	Statement Amount	Navision Amount	Difference	Entry No_
6083	01-10-2016	01-10-2016	0	200000	-200000	244260673

Case 2 : Transaction ID = BA0020191610030001 (Result is correct because amount 529023 is against a Transaction id)

Transaction Id	Value Date	Posting Date	Statement Amount	Navision Amount	Difference	Entry No_
BA0020191610030001	03-10-2016	06-10-2016	529023	200000	329023	247744313
BA0020191610030001	03-10-2016	06-10-2016	0	210000	-210000	247744316

Expression for Statement Amount :-

=sum(aggr(sum([Transaction Amount(INR)]),[Transaction ID]))

Table Data:-

Value Date	Transaction Id	Transaction Amount(INR)
01-10-2016	6083	200000
03-10-2016	BA0020191610030001	529023

Problem is for the Case 1 Statement Amount = 0 it should be 200000

Reason behind doing Aggregation is because my data is on the basis of Transaction ID and not on the basis of Entry No.

Please help me.

Thank you,

Girish

Re: Facing issue in Aggr() in Qv12

sunny_talwar — Wed, 30 Nov 2016 13:33:28 GMT

Where is Transaction Amount here? Would you be able to share more information here or even a sample?

Re: Facing issue in Aggr() in Qv12

Anonymous — Wed, 30 Nov 2016 13:35:24 GMT

In the table data i have mentioned the Transaction Amount.

I have shared you the 2 cases where in Case 1 amount is coming wrong and in the second case amount is coming correct.

Table Data:-

Value Date	Transaction Id	Transaction Amount(INR)
01-10-2016	6083	200000
03-10-2016	BA0020191610030001	529023

Re: Facing issue in Aggr() in Qv12

sunny_talwar — Wed, 30 Nov 2016 13:38:51 GMT

Why won't Sum([Transaction Amount(INR)]) work? If you have Transaction Id as your dimension, it should work unless I am missing something here

Re: Facing issue in Aggr() in Qv12

Anonymous — Wed, 30 Nov 2016 13:40:38 GMT

agreed but i want that Amount should be repeated only once because my transaction amount is on the basis of Transaction id and not on the basis of Entry no.

Re: Facing issue in Aggr() in Qv12

Anonymous — Wed, 30 Nov 2016 13:44:14 GMT

Your expression [=sum(aggr(sum([Transaction Amount(INR)]),[Transaction ID]))] seems to be fine.

Can you to share sample data? This will help us to know the reason for mismatch.

Re: Facing issue in Aggr() in Qv12

Anonymous — Wed, 30 Nov 2016 13:47:55 GMT

Yup i tried but it didn't worked.

Sample data i have shared above.

Re: Facing issue in Aggr() in Qv12

Anonymous — Wed, 30 Nov 2016 13:50:10 GMT

Friends above issue solved by adding all field in the dimension in the aggr() function.

Thank you very much Sunny and Payal for your kind help.

Girish