topic Re: How to made Calculation on Pivot Table? in QlikView

How to made Calculation on Pivot Table?

Wed, 29 Jul 2015 13:57:27 GMT

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?

settu_periasamy — Wed, 29 Jul 2015 14:41:57 GMT

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?

Thu, 30 Jul 2015 03:33:47 GMT

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?

settu_periasamy — Thu, 30 Jul 2015 03:42:02 GMT

Hi,

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

Re: How to made Calculation on Pivot Table?

Thu, 30 Jul 2015 03:56:35 GMT

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?

settu_periasamy — Thu, 30 Jul 2015 04:25:42 GMT

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?

Thu, 30 Jul 2015 04:31:11 GMT

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?

settu_periasamy — Thu, 30 Jul 2015 04:32:44 GMT

is it possible post the sample qvw and expected output?

Re: How to made Calculation on Pivot Table?

Thu, 30 Jul 2015 04:52:07 GMT

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?

settu_periasamy — Thu, 30 Jul 2015 04:56:50 GMT

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?

Thu, 30 Jul 2015 05:13:09 GMT

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?

settu_periasamy — Thu, 30 Jul 2015 05:25:22 GMT

Re: How to made Calculation on Pivot Table?

Thu, 30 Jul 2015 06:04:28 GMT

Hi,

it is ok now. I hereby attached my qvw.

Best Regards,

Kyaw Myo Tun

Re: How to made Calculation on Pivot Table?

settu_periasamy — Thu, 30 Jul 2015 06:15:23 GMT

May be like the attached.

Re: How to made Calculation on Pivot Table?

Thu, 30 Jul 2015 07:32:18 GMT

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?

settu_periasamy — Thu, 30 Jul 2015 07:35:47 GMT

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)

Re: How to made Calculation on Pivot Table?

Thu, 30 Jul 2015 08:07:40 GMT

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

Re: How to made Calculation on Pivot Table?

settu_periasamy — Thu, 30 Jul 2015 09:02:11 GMT

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?

Thu, 30 Jul 2015 09:17:46 GMT

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?

settu_periasamy — Thu, 30 Jul 2015 09:27:55 GMT

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