Qlik Community

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
gopalopsharma
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

BUMaterial FamilyMarket SegmentCodeSum ({<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))
AXA110012329128-10
AXA1100255291280
AXA11003432912810
AXA110043529128-21
AXA210056729128-10
AXA21006732912842
AXA21007922912873
AXTotal 36282912883
ATotal 36283.24482316
BYB11008351.45228-104
BYB11009161.45228-259
BYB21010271.45228-259
BYB21011581.45228-156
BYB21012621.45228207
BYTotal 17281.45228-570
BZB31013711.50126346
BZB31014441.501260
BZB31015351.50126-115
BZB41016671.50126-58
BZB41017591.50126-231
BZTotal 25261.50126-58
BTotal 42543.24482-475
Total 78823.24482-158

Hi Gopal Sharma

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.

beeaj2011
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