sum() should exclude the conditional records

[karnd]

Hi,

I have a summation field that should exclude few record values.

the records are,when product names = 'apples' and 'oranges' then exclude in Gross Sum(totalamount).

So, Net Sum = Gross Sum({totalamount} - sum({total amount}, when Product Name='apples' and 'oranges' )

I am new to crystalXI. Please provide me the solution.

Appreciates.

 

[Turkbear]

Hi,
One way:
Create a formula like this:
If {Product-Name} IN ['apples','oranges'] then 1 else 0

Place this in the same section ( probably detail) and suppress its display...Create a SUM of this formula and then use that summary to create your NET SUM formula like you described.

If, however, you only want a NET SUM ( that is all but apples and oranges) and do not need the gross one, then reverse the formula :

If {Product-Name} IN ['apples','oranges'] then 0 else 1

and use a SUM of this to show the Net Sum





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

If you are dealing with amounts or quantities, then you could adjust Turkbear's suggestion to:

If not({Product-Name} IN ['apples','oranges']) then {table.amt}

When you insert a sum on this, you would get the total amount for all products except apples and oranges.

-LB

 

[karnd]

Hi,

thanks for your quick response.

But i am using an existing summarised field and wanted to apply the above condition.

so per say, if total sum (Amount) = $1000 when group by on Product Name field.And, it also excluded the amount that is because of Product Name in (Apples,Oranges) which comes to $300. So i wanted to exclude this amount from total $1000.So the results should show as $700.
Please help me.
Thanks

 

[karnd]

sorry, i posted the thread after i have not seen lbass. let me try using your suggession.
thanks

 

[karnd]

thanks for your input.