Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Expert,
Can I make Calculation on Pivot Table? Let me explain the process.
In this table, Firstly, I would like to get Total Buying Amount and Total Selling Amount for all date. So, I made a pivot table that have two expression is 1) sum(Buying Amount) 2) sum(Selling Amount) against ID. After that I would like to get Closing Balance for each ID. for exp, Closing balance for 111 is 900 , It can get in original table easily but I would like to know how to calculate it on Pivot table?
Best Regards,
Kyaw Myo Tun
Dear settu,
I can only see @Mention and !App at the moment. where I need to go to see that "Attach"?
Regards,
Kyaw Myo Tun
Hi,
it is ok now. I hereby attached my qvw.
Best Regards,
Kyaw Myo Tun
May be like the attached.
Dear settu,
I can't open your attached qvw because I am using the personal edition at the moment. Can you explain me on text pane?
Regard,
Kyaw Myo Tun
Sure.
Dimension -> ID
Expressions:
Buying Amount -> aggr(sum([Buying Amount]),Id)
Selling Amount -> aggr(sum([Selling Amount]),Id)
Closing -> (aggr(sum([Opening Amount]),Id)+aggr(sum([Selling Amount]),Id))-aggr(sum([Buying Amount]),Id)
Dear settu,
Firstly, It worked well but I added one more date for ID-111 with some amount and the "Closing" showed the wrong amount. Please see below.
Excel File:
Qvw result:
I am looking forward your reply.
Best Regards,
Kyaw Myo Tun
Hi,
i don't know what is wrong on it. for ID 111 (1300 + 1200) - 700 = 800, right?
or any other output?
Dear settu,
I think we need to fix the start for each ID "Opening Amount" for (01.06.2015) but some ID may start from (02.06.2015) and then aggregate "Buying Amount" & "Selling Amount" and make the calculation(our formula) on it. But I don't know technically how to do it.
Best Regards,
Kyaw Myo Tun
Hi Kyaw Myo,
Can you tell us the output data (dimension and expression ) you need to show for the below input?
Input:
Date | Id | Name | Opening Amount | Buying Amount | Selling Amount |
01.06.2015 | 111 | KKT | 300 | 200 | 400 |
01.06.2015 | 112 | AAM | 1000 | 300 | 500 |
02.06.2015 | 111 | KKT | 400 | 300 | 500 |
02.06.2015 | 112 | AAM | 1200 | 800 | 300 |
03.06.2015 | 111 | KKT | 600 | 200 | 300 |