topic Re: Sum not adding up correctly in QlikView

Sum not adding up correctly

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

When I sum the [Total Hours] field by [Project Name] and [Date Formatted], it is not correctly added together because some of the rows in the excel sheet are the exact same. I need to find a way to make each row unique and being able to sum the [Total Hours] by the same [Date Formatted] and [Project Name]. It also needs to be able to calculate the average [Demand Hours] correctly and of right now it is, but that is just something to keep in mind when making changes. The Actual Hours sum should equal 31.75 and the average Remaining Allocated Hours should be 52.69. I also have to use DISTINCT because otherwise the numbers are way off. I have attached a sample Qlikview file and a sample Excel file. Any suggestions would be greatly appreciated!

Thanks!

Re: Sum not adding up correctly

sunny_talwar — Tue, 20 Jun 2017 13:27:26 GMT

Remaining Allocated Hours are already 52.69... so is that right? For Actual Hours, try this

Sum(Aggr([Total Hours], RowNumber))

Re: Sum not adding up correctly

felipedl — Tue, 20 Jun 2017 13:35:49 GMT

Hi Caitlyn,

Your outter join is multiplying the lines for [Total hours] field.

Instead of joining, create a link between the two tables and then do the expressions, like the attached example.

Hope it helps,

Felipe.

Re: Sum not adding up correctly

Tue, 20 Jun 2017 15:26:11 GMT

Thanks for the quick reply! It did exactly what I needed it to do!