Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution.
**READ ALL ABOUT IT!**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Re: Pivot Table miscalculation

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Not applicable

2014-03-04
03:05 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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,264 Views

1 Solution

Accepted Solutions

Not applicable

2014-03-04
03:50 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

957 Views

8 Replies

gopalopsharma

Creator

2014-03-04
03:22 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hello Oguz,

Can you share some explanation of these expressions?

957 Views

Not applicable

2014-03-04
03:24 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

)

)

957 Views

Not applicable

2014-03-04
03:40 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

957 Views

Not applicable

2014-03-04
03:46 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

957 Views

Not applicable

2014-03-04
03:46 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

BU | Material Family | Market Segment | Code | Sum ({<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)) |

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 |

Hi Gopal Sharma

You can see the detailed table and expressions here.

Thanks

957 Views

Not applicable

2014-03-04
03:50 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

958 Views

beeaj2011

Contributor III

2014-03-04
03:57 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

957 Views

Not applicable

2014-03-04
04:03 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

You are definitely right. I have been suffocating with formulas and couldn't think simple.

Sorry I waste your time little bit.

Thanks.

Oguz

957 Views