topic Re: Averages: Remove top and bottom 10% in QlikView

Averages: Remove top and bottom 10%

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Averages: Remove top and bottom 10%

Anonymous — Thu, 15 Jun 2017 15:24:51 GMT

Perhaps create an indicator on my source data to capture top and bottom 10%

Re: Averages: Remove top and bottom 10%

sunny_talwar — Thu, 15 Jun 2017 15:25:20 GMT

May be use Fractile functions to remove Top 10 and Bottom 10 from the overall observation and then use the remaining data to calculate your average?

Re: Averages: Remove top and bottom 10%

Anonymous — Thu, 15 Jun 2017 16:41:08 GMT

I have two example and neither are working. Can you help with the syntax?

//,AVG({<D = {"> $(=Fractile(FIRST_CONTACT_CAL_DAYS,0.20)) <= $(=Fractile(FIRST_CONTACT_CAL_DAYS,0.40))"}>} DISTINCT D)

//avg(if([FIRST_CONTACT_CAL_DAYS] > aggr(NODISTINCT fractile(FIRST_CONTACT_CAL_DAYS, 0.1), D ) and [FIRST_CONTACT_CAL_DAYS] < aggr(NODISTINCT fractile(FIRST_CONTACT_CAL_DAYS, 0.9), CLT_CLT_PGMID ),D))

Re: Averages: Remove top and bottom 10%

sunny_talwar — Thu, 15 Jun 2017 16:42:57 GMT

Will be difficult without some data to work with... Also, is this something you want in the script or front end?

Re: Averages: Remove top and bottom 10%

Anonymous — Thu, 15 Jun 2017 16:48:36 GMT

I get some data

Re: Averages: Remove top and bottom 10%

Anonymous — Thu, 15 Jun 2017 17:11:07 GMT

See attached sample data

Re: Averages: Remove top and bottom 10%

sunny_talwar — Thu, 15 Jun 2017 17:12:26 GMT

What is the number you expect to see here?

Re: Averages: Remove top and bottom 10%

Anonymous — Thu, 15 Jun 2017 17:25:02 GMT

I'm trying to capture the average column

FIRST_CONTACT_CAL_DAYS but want to exclude the top and bottom 10% to exclude the outliers.

Re: Averages: Remove top and bottom 10%

sunny_talwar — Thu, 15 Jun 2017 17:59:55 GMT

Is 0 an outlier you would want to exclude? Because it might be one of the bottom 10%?

Re: Averages: Remove top and bottom 10%

Anonymous — Thu, 15 Jun 2017 18:11:59 GMT

CORRECT

Re: Averages: Remove top and bottom 10%

sunny_talwar — Thu, 15 Jun 2017 18:19:39 GMT

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))

Re: Averages: Remove top and bottom 10%

Anonymous — Thu, 15 Jun 2017 19:20:56 GMT

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?

Re: Averages: Remove top and bottom 10%

sunny_talwar — Thu, 15 Jun 2017 19:30:06 GMT

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))