Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Where is Transaction Amount here? Would you be able to share more information here or even a sample?
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 |
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
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.
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.
Yup i tried but it didn't worked.
Sample data i have shared above.
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