sum of two fields but omit record if one field is zero

[#1]

I have two fields in my straight table that I need to add and divide one by the other but unless there is a value in both fields I don't want to add that record to the total.  I need the entire record omited unless there is a value in both fields.  So in the below.

field 1   field 2

6             4

7             5

5             0

field1 sum = 18

field2 sum = 9

but fied1/field2 = 13/9 because record 3 is omited.

any suggestions?

[#2]

In your load script, use an IF statement, like IF(field2=0,0,field1).

This will make the field1 corresponding to a 0 field2 as 0.

Then add and divide as usual.

Cheers

[CELAMBARASAN · Accepted solution]

Hi,

     In load script use

     Load

          Field1,

          Field2

     From..

     Where Field1<>0 AND Field2<>0;

     Otherwise use in presentation as

     =Sum({<Field2-={0}>} Field1)/Sum({<Field1-={0}>} Field2)

Hope it helps

Celambarasan

[jagan]

Hi,

For this you can filter those records in script by using

TableName:

Load

          Field1,

          Field2

From DataSourceName

Where Field1 <> 0 AND Field2<>0;

or if you want to filter this in set analysis, you can use

=Sum({<Field1-={0}, Field2-={0}>} Field1)/Sum({<Field2-={0}, Field1-={0}>} Field2)

Regards,

Jagan.