Qlik Community

Ask a Question

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 
Search instead 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.

View solution in original post

8 Replies
Creator
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

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

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

View solution in original post

Contributor III
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