Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

26 Replies
Not applicable
Author

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

settu_periasamy
Master III
Master III

Capture.JPG

Not applicable
Author

Hi,

   it is ok now. I hereby attached my qvw.

Best Regards,

Kyaw Myo Tun

settu_periasamy
Master III
Master III

May be like the attached.

Not applicable
Author

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

settu_periasamy
Master III
Master III

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)

Capture.JPG

Not applicable
Author

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

settu_periasamy
Master III
Master III

Hi,

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

or any other output?

Not applicable
Author

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

settu_periasamy
Master III
Master III

Hi Kyaw Myo,

Can you tell us the output data (dimension and expression ) you need to show for the below input?

     Input:

DateIdNameOpening AmountBuying AmountSelling Amount
01.06.2015111KKT300200400
01.06.2015112AAM1000300500
02.06.2015111KKT400300500
02.06.2015112AAM1200800300
03.06.2015111KKT600200300