topic Re: Pivot Table miscalculation in QlikView

Pivot Table miscalculation

Tue, 04 Mar 2014 08:05:46 GMT

Hi,

I have a problem with pivot table. Sum of rows doesn't work on column which I highligted (yellow column). I tried everything that came to my mind. I took apart the formula, used aggr() but couldn't get the right result.

I have added the QVW file. You will see solutions of mine which didn't work on the right.

Thanks

Re: Pivot Table miscalculation

gopalopsharma — Tue, 04 Mar 2014 08:22:09 GMT

Hello Oguz,

Can you share some explanation of these expressions?

Re: Pivot Table miscalculation

Tue, 04 Mar 2014 08:24:45 GMT

Try

sum(

aggr(

(Sum({<Year={'$(=max(Year))'}>}Pieces)-Sum ({<Year={'$(=max(Year)-1)'}>}Pieces))

*((Sum ({<Year={'$(=max(Year)-1)'}>}[Net Sales]))/(Sum ({<Year={'$(=max(Year)-1)'}>}Pieces)))

,Code

)

Re: Pivot Table miscalculation

Tue, 04 Mar 2014 08:40:44 GMT

ylchuang I've tried but didn't work.

sum of other columns is correct. just one column suppose to be 84 but qlikview and excel shows 84.

I have even calculate with calculator by one by it suppose to be 84.

Thanks.

Re: Pivot Table miscalculation

Tue, 04 Mar 2014 08:46:06 GMT

Do you want the sum of above? or you want expression sum?

Re: Pivot Table miscalculation

Tue, 04 Mar 2014 08:46:36 GMT

BU	Material Family	Market Segment	Code	Sum ({<Year={'2013'}>}Pieces)	Sum ({<Year={'2012'}>}Pieces)	Sum (total <[Material Family]>{<Year={'2012'}>}[Net Sales])	Sum (total <[Material Family]>{<Year={'2012'}>}Pieces)	(Column(1)-Column(2)) * (Column(3)/Column(4))
A	X	A1	1001	2	3	291	28	-10
A	X	A1	1002	5	5	291	28	0
A	X	A1	1003	4	3	291	28	10
A	X	A1	1004	3	5	291	28	-21
A	X	A2	1005	6	7	291	28	-10
A	X	A2	1006	7	3	291	28	42
A	X	A2	1007	9	2	291	28	73
A	X	Total		36	28	291	28	83
A	Total			36	28	3.244	82	316
B	Y	B1	1008	3	5	1.452	28	-104
B	Y	B1	1009	1	6	1.452	28	-259
B	Y	B2	1010	2	7	1.452	28	-259
B	Y	B2	1011	5	8	1.452	28	-156
B	Y	B2	1012	6	2	1.452	28	207
B	Y	Total		17	28	1.452	28	-570
B	Z	B3	1013	7	1	1.501	26	346
B	Z	B3	1014	4	4	1.501	26	0
B	Z	B3	1015	3	5	1.501	26	-115
B	Z	B4	1016	6	7	1.501	26	-58
B	Z	B4	1017	5	9	1.501	26	-231
B	Z	Total		25	26	1.501	26	-58
B	Total			42	54	3.244	82	-475
Total				78	82	3.244	82	-158

Hi Gopal Sharma

You can see the detailed table and expressions here.

Thanks

Re: Pivot Table miscalculation

Tue, 04 Mar 2014 08:50:28 GMT

A reminder... it's actually 83,142851429... So 84 is not correct.

Re: Pivot Table miscalculation

beeaj2011 — Tue, 04 Mar 2014 08:57:02 GMT

Hello Oguz

the calculation of your vol columns is not the sum of the rows because it is a multiplication with a ratio, therefor it should be calculated row by row. I did a check and it does the recall as it should see attached XL.

Regards

Ton

Dropbox - calc_pivot.xls

Re: Pivot Table miscalculation

Tue, 04 Mar 2014 09:03:40 GMT

ylchuang

You are definitely right. I have been suffocating with formulas and couldn't think simple.

Sorry I waste your time little bit.

Thanks.

Oguz