Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

martin_hamilton
Contributor

Calculate Average based on value of 2 columns on same record

Hi

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

StatusDays
Active8878
In Active989
Active8989

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

3 Replies

Re: Calculate Average based on value of 2 columns on same record

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
Contributor

Re: Calculate Average based on value of 2 columns on same record

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
Contributor

Re: Calculate Average based on value of 2 columns on same record

hi its ok I fixed it:

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

thanks