topic Re: Sum not correct in QlikView

Sum not correct

cbaqir — Fri, 01 Nov 2019 14:55:40 GMT

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!

Re: Sum not correct

sunny_talwar — Fri, 01 Nov 2019 16:14:07 GMT

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)))

Re: Sum not correct

cbaqir — Fri, 01 Nov 2019 19:30:31 GMT

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.

Re: Sum not correct

StarinieriG — Mon, 04 Nov 2019 14:21:41 GMT

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

Re: Sum not correct

Brett_Bleess — Thu, 07 Nov 2019 20:24:26 GMT

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