Calculating an average over a dimension

[#1]

Hi everyone,

I've got a feeling that I'm missing something really obvious with this one.

Consider the following data:

Site	Value
A	10
A	20
A	50
A	60
A	10
B	100
B	200
B	500
B	600
B	100

What do I need to do in order to flag up values that are higher than the average value FOR THAT SITE?

I've tried IF(VALUE>AGGR(AVG(VALUE), SITE),1,0)  but that doesn't work and I've also tried using a variable, but that only works when 1 site is selected.

Ideally, I need to work this expression into my script, as opposed to a chart.

Many thanks in advance,

Robin

[swuehl · Accepted solution]

Maybe like this:

INPUT:

LOAD Site,

     Value

FROM

[http://community.qlik.com/thread/65685?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

AVG:

join LOAD Site, avg(Value) as Avg Resident INPUT Group by Site;

RESULT:

LOAD Site,

     Value,

//     Avg,

     if(Value>Avg,1,0) as Flag

    Resident INPUT;

drop table INPUT;

[#3]

Superb - many thanks.