topic Re: Calculating an average over a dimension in QlikView

Calculating an average over a dimension

Wed, 07 Nov 2012 10:45:39 GMT

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

Re: Calculating an average over a dimension

swuehl — Wed, 07 Nov 2012 10:57:32 GMT

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;

Re: Calculating an average over a dimension

Wed, 07 Nov 2012 11:42:42 GMT

Superb - many thanks.