create a rate on certains lines

[Fred12]

Hello, I got a table like that : 

FIELD 1   FIELD 2   DIFFERENCE

50              40                  10

40              100                -60

30              70                  -40

20              0                   20

I want a rete when difference is negative, then : 

(40+30)/(100+70) = 70/170

I tried used avg(if) like that : 

=avg(if(sum( {$<[Um/Spencours:0=Encours,1=Soldée]={'0'}>} [Quantitéphysiqueencours] )<0,0,1))

Thx 

[ogster1974]

in your script create a flag identifying the rows that have neg difference.

If([FIELD 1]<[FIELD 2],1,0) as [NEG DIFF]

Then in your front end you can use

Sum({<[NEG DIFF]={1}>} [FIELD 1])/Sum({<[NEG DIFF]={1}>}[FIELD 2]) as your NEG RATE

[anat]

table:

load * Inline [
f1,f2

50,40
40,100
30,70
20,0
];
NoConcatenate
table1:
LOAD f1,f2,if(f1>f2,f1-f2,sum(f1+Previous(f1))/sum(f2+Previous(f2))) as d Resident table group by f1,f2 Order by f1;

DROP Table table;

[Fred12]

I tried your solution but it doesn't work, it put me only 0 for my negative number

[Fred12]

I got set analysis in my formula. I just didn't put it to simplify thing

[Fred12]

I dit that in my pagina qliksense  :

sum( {$<[Um/Spencours:0=Encours,1=Soldée]={0}>} [Quantitéphysiqueencours] )-sum( {$<[0=Stockdispo,1=Stocknondispo]={0}>} [Quantitéréservée/Enpréparation] )

It's for my difference.

And after in my kpi, I tried this : 

if (sum( {$<[Um/Spencours:0=Encours,1=Soldée]={0}>} [Quantitéphysiqueencours] )-
sum( {$<[0=Stockdispo,1=Stocknondispo]={0}>} [Quantitéréservée/Enpréparation] ) <0,

sum( {$<[Um/Spencours:0=Encours,1=Soldée]={0}>} [Quantitéphysiqueencours] )/
sum( {$<[0=Stockdispo,1=Stocknondispo]={0}>} [Quantitéréservée/Enpréparation]))

[ogster1974]

The solution does work you must be applying it incorrectly see.

TMP:
load * Inline [
FIELD 1,FIELD 2
50,40
40,100
30,70
20,0
];

NoConcatenate
Final:
Load
*,
If([FIELD 1]<[FIELD 2],1,0) as [NEG DIFF]
;
Load
[FIELD 1],
[FIELD 2],
[FIELD 1]-[FIELD 2] AS DIFFERENCE
Resident TMP
;

dROP TABLE TMP;

Then in the front end apply the set analysis vs your hard coded logic.  It tallies to the same rate