# New to QlikView

Discussion board where members can get started with QlikView.

Highlighted Not applicable

## Pivot Table miscalculation

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

1 Solution

Accepted Solutions Not applicable

## Re: Pivot Table miscalculation

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

8 Replies
Contributor

## Re: Pivot Table miscalculation

Hello Oguz,

Can you share some explanation of these expressions? Not applicable

## Re: Pivot Table miscalculation

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

)

) Not applicable

## Re: Pivot Table miscalculation

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. Not applicable

## Re: Pivot Table miscalculation

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

## Re: Pivot Table miscalculation

 BU Material Family Market Segment Code Sum ({}Pieces) Sum ({}Pieces) Sum (total <[Material Family]>{}[Net Sales]) Sum (total <[Material Family]>{}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

You can see the detailed table and expressions here.

Thanks Not applicable

## Re: Pivot Table miscalculation

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

New Contributor III

## Re: Pivot Table miscalculation

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 Not applicable

## Re: Pivot Table miscalculation

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