Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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
Author

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

View solution in original post

8 Replies
gopalopsharma
Creator
Creator

Hello Oguz,

Can you share some explanation of these expressions?

Not applicable
Author

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
Author

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
Author

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

Not applicable
Author

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
Author

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

beeaj2011
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
Author

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