Sum not adding up correctly

[#1]

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!

[sunny_talwar · Accepted solution]

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

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

[felipedl]

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.

[#4]

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