topic Trimmed Average in QlikView

Trimmed Average

holmlund — Mon, 19 May 2014 09:33:59 GMT

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 — Mon, 19 May 2014 09:39:21 GMT

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 — Mon, 19 May 2014 10:11:13 GMT

Unfortunately didn't work

ThornOfCrowns — Mon, 19 May 2014 10:25:05 GMT

Because..?

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

hic — Mon, 19 May 2014 10:25:24 GMT

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