topic Sum of negetive value in Text Object in QlikView

Sum of negetive value in Text Object

Fri, 26 Jul 2013 09:07:35 GMT

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

Thanks,

Veer.

antoinelaviron — Fri, 26 Jul 2013 09:30:07 GMT

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 — Fri, 26 Jul 2013 10:20:21 GMT

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)

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

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

Tue, 30 Jul 2013 06:50:50 GMT

Thank u i got the rite method