# New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a \$200 Amazon Gift Card! Watch Video
cancel
Showing results for
Did you mean:
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

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

8 Replies
Creator

Hello Oguz,

Can you share some explanation of these expressions?

Not applicable

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

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

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

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

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

Contributor III

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

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

Tags