26 Replies Latest reply: Aug 1, 2015 3:30 AM by kyaw myo tun

# How to made Calculation on Pivot Table?

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

• ###### Re: How to made Calculation on Pivot Table?

Hi,

Can you give the closing balance formula? what is your dimensions?

is it possible to post the sample qvw to understand better?

• ###### Re: How to made Calculation on Pivot Table?

Dear settu,

I would like to get the closing balance for each Id. The formula is so sample that ID- 111 (Opening Amount + Selling Amount - Buying Amount) for each date. The next date's Opening Amount will be previous date's Closing Amount. Please see at the above table.

Best Regards,

Kyaw Myo Tun

• ###### Re: How to made Calculation on Pivot Table?

Hi,

Can you tell about the 5th row, how the closing balance as 900?

• ###### Re: How to made Calculation on Pivot Table?

Dear settu,

That is my mistake and the closing amount will be 1500. Sorry for my mistake.

Best Regards,

Kyaw Myo Tun

• ###### Re: How to made Calculation on Pivot Table?

is there any specific aggregation to do this, if not,

you can directly apply the below formula, right?

=(Sum(Opening Amount) + Sum(Selling Amount))-Sum(Buying Amount)

find the attached for reference

• ###### Re: How to made Calculation on Pivot Table?

Dear settu,

I have some aggregations in my pivot table as I mentioned above and I also want to do calculation on that aggregated table.

Regards,

Kyaw Myo Tun

• ###### Re: How to made Calculation on Pivot Table?

is it possible post the sample qvw and expected output?

• ###### Re: How to made Calculation on Pivot Table?

Dear settu,

I am trying to upload the qvd but I can't see the attach button. Can you advice me please?

Regards,

Kyaw Myo Tun

• ###### Re: How to made Calculation on Pivot Table?

When you are replying, top right corner in the reply window, there is option , Use Advanced editor.

click that. then, in the bottom right corner, there is attach symbol

• ###### Re: How to made Calculation on Pivot Table?

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

• ###### Re: How to made Calculation on Pivot Table?

Hi,

it is ok now. I hereby attached my qvw.

Best Regards,

Kyaw Myo Tun

• ###### Re: How to made Calculation on Pivot Table?

May be like the attached.

• ###### Re: How to made Calculation on Pivot Table?

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

• ###### Re: How to made Calculation on Pivot Table?

Sure.

Dimension -> ID

Expressions:

Selling Amount -> aggr(sum([Selling Amount]),Id)

Closing -> (aggr(sum([Opening Amount]),Id)+aggr(sum([Selling Amount]),Id))-aggr(sum([Buying Amount]),Id)

• ###### Re: How to made Calculation on Pivot Table?

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:

Best Regards,

Kyaw Myo Tun

• ###### Re: How to made Calculation on Pivot Table?

Hi,

i don't know what is wrong on it. for ID 111 (1300 + 1200) - 700 = 800, right?

or any other output?

• ###### Re: How to made Calculation on Pivot Table?

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

• ###### Re: How to made Calculation on Pivot Table?

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
• ###### Re: How to made Calculation on Pivot Table?

Dear settu,

I made a mistake again in my excel file about "Opening Amount". Here is the correct excel file. I apologize again for my mistake.

Best Regards,

Kyaw Myo Tun

• ###### Re: How to made Calculation on Pivot Table?

Hi,

Can you tell, Which dimensions you want to show in the Table,

ID, Date, Name?

• ###### Re: How to made Calculation on Pivot Table?

It seems you want your calculation against ID dimension only, is this correct?

• ###### Re: How to made Calculation on Pivot Table?

Dear Experts,

Sorry for late reply. Yes, I only want to calculate on Id but I have to consider on Date because some Ids did not start from 01.06.2015. What is the best way to calculate for getting Closing Amount on pivot table? Kindly to see below.

Best Regards,

Kyaw Myo Tun

• ###### Re: How to made Calculation on Pivot Table?

How you are calculating closing amounts in Blue?Please  Explain the logic

• ###### Re: How to made Calculation on Pivot Table?

Dear Kush,

It is sample and the closing amounts based on each Id. But, one thing to notice is each Id may start from different date.for exp, Id-113 start from 02.06.2015 and you will see the Opening amount. The calculation for closing amount is (Opening Amount + Selling Amount - Buying Amount). I noticed that I need to keep each Id's Opening Amount and make calculation on each Id like([Opening Amount for an ID]+ aggr(sum([Selling Amount])- aggr(sum([Buying Amount]). Another one is, I've done other calculation on pivot tabla(such as [Total Buying Amount] and [Total Selling Amount].But I don't know how to do it.

Best Regards,

Kyaw Myo Tun

• ###### Re: How to made Calculation on Pivot Table?

Dear Experts,

Sorry for late reply. Yes, I only want to calculate on Id but I have to consider on Date because some Ids did not start from 01.06.2015. What is the best way to calculate for getting Closing Amount on pivot table? Kindly to see below.

Best Regards,

Kyaw Myo Tun