Trimmed Average

[holmlund]

I have a expression  avg(SIGNDATE-EXAMDATE)  which includes some faulty extreme values.

I only want to average values where SIGNDATE-EXAMDATE is less than 30 days.

How can I remove these extremevalues from my result?

[its_anandrjs]

Update the script

You can try with the below script previous script will not work

Data:

LOAD Date#(SIGNDATE,'DD/MM/YYYY') as SIGNDATE,Date#(EXAMDATE,'DD/MM/YYYY') as EXAMDATE;

LOAD * Inline

[

SIGNDATE,EXAMDATE

01/05/2014,31/05/2014

01/05/2014,15/05/2014

];

LOAD

Avg( if( SIGNDATE-EXAMDATE < 30, SIGNDATE-EXAMDATE )) as Avg

Resident Data;

DROP Table Data;

[holmlund]

Unfortunately didn't work

[ThornOfCrowns]

Because..?

Did it return data, but incorrectly or not return anything at all?

[hic · Accepted solution]

It doesn't work since the if statement is evaluated outside the Avg() function. If you put it inside the Avg() it will probably work:

     Avg( If( SIGNDATE-EXAMDATE < 30 , SIGNDATE-EXAMDATE) )

HIC