topic Problem with SUM(IF()) in QlikView

Problem with SUM(IF())

Anonymous — Wed, 05 Oct 2011 09:32:49 GMT

Hello,

i have some problem with the expression SUM (IF()).

I have a table like this:

IDFIELD CURR_ENERGY

0 188716.5

1 95533

2 94350.8

i want the sum of the fields different from 0. i wrote the follow expression : Sum(if(IDFIELD<>0,CURR_ENERGY,0))

the result is : 61'522'351.20 insted of the sum of 95533 with 94350.8

So i have tried to make the SUM of the FIELD equal to 0 : Sum(if(IDFIELD=0,CURR_ENERGY,0))

and the result was 377'473 that is twice the value of FIELD 0

Can anybody explain me why happend this?

Thanks,

Luca

Sokkorn — Wed, 05 Oct 2011 09:46:17 GMT

Hi Luca,

I think that your data source have duplicate record. Make sure check your data source to find a duplicate row.

Or try this SUM(DISTINCT IF(IDFIELD<>0,CURR_ENERGY,0))

Regards,

Sokkorn

Anonymous — Wed, 05 Oct 2011 12:27:26 GMT

Thank Sokkorn,

DISTINCT works good.

You were also right on the datasource, there was another MySql record with IDFIELD = NULL.

Qlik does not show it but in the expression seems to use it with the value of the record with IDFIELD = 0...

Regards,

Luca