4 Replies Latest reply on Aug 13, 2010 4:02 PM by fitch

# sum of positive value only

### Title

sum of positive value only

### Post

I have Product table and ProductLine table.  Product table links to ProductLine by SKU with one to multiple linkage.  Here is the fields on each table

Product:

SKU

Description

SumOnHand = sum(ProductLine::OnHand)

SumOnPO = sum(ProductLine::OnPO)

SumOnSO = sum(ProductLine::OnSO)

ProductLine

SKU (linked to Product)

OnHand

OnPO

OnSO

All of Sum fields work correct.  If I want to sum the positive value of OnSO only.  How can I do it? For example, I have OnSO five records and values are 12, 40, 3, -10, -3.  Current the value of SumOnSO is 42 (=12+40+3-10-3).  I need a calculation which sums positive value only.  It will be 55(12+40+3).

• ###### 1. Re: sum of positive value only

Separate the postive and negative values into separate fields. Bookkeepers were doing that long before computers by setting up ledgers with debit and credit columns.

If you don't want to restructure your database and layouts to separate the data fields, you can add calculation fields that separate the values behind the scenes.

Defind a calc, cPositiveOnSO in ProductLine as

If ( OnSO > 0 ; OnSO ; 0 )  // if you leave out " ; 0" this field will be empty for negative and zero and this also works.

use Sum(ProductLine::cPositiveOnSo) to compute the sum of just the positive values.

• ###### 2. Re: sum of positive value only

Hi

"I want to sum the positive value of OnSO only"

You could even try a calculation like this:

Evaluate ( Substitute (  List ( ProductLine::OnSO ) ; [ "-" ; "0*" ] ; [ ¶ ; "+" ] ) )

• ###### 3. Re: sum of positive value only

That's clever!

• ###### 4. Re: sum of positive value only

Excellent calc!