Averages: Remove top and bottom 10%

[Anonymous]

I have the below script that calculates the averages of business days and calendar days. The problem, I have outlier values that are skewing results.

Can I:

Remove top 10% from the average?

Remove top 10% and bottom 10% from the average?

LOAD

D AS CONTACT_D,
Avg([FIRST_CONTACT_CAL_DAYS])AS Avg_FirstPhoneContact_Days,
Avg([FIRST_CONTACT_WK_DAYS])AS Avg_FIRST_CONTACT_WK_DAYS

FROM $(vPath_QVDs)MainTable.qvd(qvd)

Where 1=1
AND NOT IsNull(FIRST_CONTACT_CAL_DAYS)
AND NOT IsNull(FIRST_CONTACT_WK_DAYS)
AND START_DT > Addmonths(today(),-12)

GROUP BY
D

[Anonymous]

CORRECT

[sunny_talwar]

Try this

Avg(Aggr(If(FIRST_CONTACT_CAL_DAYS >= Fractile(TOTAL <PROGRAM> FIRST_CONTACT_CAL_DAYS, 0.10) AND FIRST_CONTACT_CAL_DAYS <= Fractile(TOTAL <PROGRAM> FIRST_CONTACT_CAL_DAYS, 0.90), FIRST_CONTACT_CAL_DAYS), PROGRAM, id))

[Anonymous]

I need to add another filter in the expression.

Something like below:

ASSIGNED_STATUS = 'NEWLY ASSIGNED MEMBER'

Can I easily add that into the expression?

[sunny_talwar · Accepted solution]

Should be simple

Avg({<ASSIGNED_STATUS = {'NEWLY ASSIGNED MEMBER'}>} Aggr(

If(Only({<ASSIGNED_STATUS = {'NEWLY ASSIGNED MEMBER'}>} FIRST_CONTACT_CAL_DAYS) >=

Fractile({<ASSIGNED_STATUS = {'NEWLY ASSIGNED MEMBER'}>} TOTAL <PROGRAM> FIRST_CONTACT_CAL_DAYS, 0.10) AND

Only({<ASSIGNED_STATUS = {'NEWLY ASSIGNED MEMBER'}>} FIRST_CONTACT_CAL_DAYS) <=

Fractile({<ASSIGNED_STATUS = {'NEWLY ASSIGNED MEMBER'}>}TOTAL <PROGRAM> FIRST_CONTACT_CAL_DAYS, 0.90),

FIRST_CONTACT_CAL_DAYS), PROGRAM, id))