Sum not correct

[cbaqir]

My expression is: num(if(PRIMARY_ASSIGNEE=CCL_RESOURCE, sum(SUB_BUILD_NP_EST_CCL),0))

If the PRIMARY_ASSIGNEE = gperez016 and the DEFECT_ID is 8519, the total for this expression should be 16 but it's coming through as 320. 

Any help is appreciated. Thanks!

[sunny_talwar]

Seems like there is a duplication problem here... adding DISTINCT helps, but might not be the best way to do this

num(if(PRIMARY_ASSIGNEE=CCL_RESOURCE, sum(DISTINCT SUB_BUILD_NP_EST_CCL),0))

 or this

Num(Sum(If(PRIMARY_ASSIGNEE = CCL_RESOURCE, SUB_BUILD_NP_EST_CCL)))

[cbaqir]

Wouldn't distinct just count all of the numbers once? If it really was 16 for two tickets, I wouldn't want it to ignore one.

Each DEFECT_ID has 1 PRIMARY_ASSIGNEE but many different builders. For example, builders have a specialty like CLINDOC, CCL, CPOE, CHARGES, CORE etc...
Each PRIMARY_ASSIGNEE could also be 1 OR MORE builder specialty. For example, the PRIMARY_ASSIGNEE gperez016 could have CCL hours and CLINDOC hours.
My requirement is to show each user anytime they are listed as a primary assignee and then total the specialty hours.

[StarinieriG]

Hi,

if you use this expression: Count(EST_NON_PROD_BUILD_HRS) it will show that there are 20 rows with 16, so 320 it seems to be correct

[Brett_Bleess]

Cassandra, one trick to show everything is in the load script, use the RecNo() or RowNo() functions to put a unique value on each row, that way the Pivot Table will show every row etc.  You can use that to check things and then remove it once you are confident things are working as you want.  Hopefully the other guys can confirm this is a decent way to try double-check things as well.

Cheers,
Brett