Sum of negetive value in Text Object

[#1]

Hello,

I want to calculate negetive profit as show below

Profit= Income -Cost

I want the result in text box as -15 using set analysis

Income	Cost	Profit	Negetive profit
25	10	15
15	20	-5	-5
50	30	20
60	40	20
40	50	-10	-10
		40	-15

Thanks,

Veer.

[antoinelaviron]

Hi try this in a texbox:

=sum(

     Aggr( if((Cost<Income),(Income-Cost),0), Income,Cost)

     )

or if you have the field Profit already calculated and want to use set analysis

=sum(

     Aggr( only({$ <Profit = {'<0'}>} Profit)

               ,Income,Cost

          )

     )

Best,

Antoine

[swuehl · Accepted solution]

Try

sum(if(Income-Cost < 0, Income-Cost))

edit: If you want to use set analysis, you can create a flag in your load script

LOAD

     Income,

     Cost,

     if( Income-Cost <0 ,1,0) as Flag,

     ...

FROM ...;

then in your text box, either use

sum( (Income-Cost) * Flag)

or

sum({<Flag = {1}>} Income-Cost)

Looking at these expressions, it would probably be good to calculate the difference itself also in the script.

[#4]

Thank u i got the rite method