Calculate Average based on value of 2 columns on same record

[martin_hamilton]

Hi

I have a set of data which looks like the following:

Status	Days
Active	8878
In Active	989
Active	8989

I wish to calculate the average on the column entitled Days when column entitled Status equals Active

I tried to construct the following but its not working:

=Avg(Aggr(If([Status]) = 'Active', Avg([Days])))

The expression is looking ok but its not returning a value.

Any ideas? have I even got the expression in the correct format?

thanks

Martin

[marcus_sommer]

There is at least one dimension missing against the aggr could be calculated whereby I don't think that you need them for the described case. Just try:

Avg({< [Status]) = {'Active'}>} [Days])

- Marcus

[martin_hamilton]

Hi Marcus

I am getting an error; Error in set modifier expression but have amended to the below but still doesnt seem to be calculating correctly?

=Avg(${< [STATUS]) = {'ACTIVE'}>} [DAYS])

Any ideas?

My inline data is below;

INLINE:

LOAD * INLINE [

    STATUS,DAYS

    ACTIVE,6

    NOT ACTIVE,6

    ACTIVE,8

    ACTIVE,8

];

thanks

Martin

[martin_hamilton]

hi its ok I fixed it:

Avg({1<[STATUS] = {ACTIVE}>} [DAYS])

thanks