# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

New Contributor III

## average, ignoring outliers, and current selection

I'm having an issue with this formula and need some help.  This calculates the average usage by market for accounts in the 10-90 percentile. This expression works just fine as long as I don't select a customer number.  Once I select a customer number this line goes blank.  I am comparing the average usage for the market against the selected customer number.

avg(

aggr(

if((count({1<Customer_Number = ,Order_type = {Purchased}>}Total  <Market> Distinct Customer_Number)

-Rank(Sum({1<Customer_Number = ,Order_type = {Purchased}>} Total <Market,Customer_Number> Usage)))

/count({1<Customer_Number = ,Order_type = {Purchased}>}Total  <Market> Distinct Customer_Number)>.09,

if((count({1<Customer_Number = ,Order_type = {Purchased}>}Total  <Market> Distinct Customer_Number)

-Rank(Sum({1<Customer_Number = ,Order_type = {Purchased}>}Total <Market,Customer_Number> Usage)))

/count({1<Customer_Number = ,Order_type = {Purchased}>}Total  <Market> Distinct Customer_Number)<.91,

Sum({1<Customer_Number = ,Order_type = {Purchased}>}Usage)))

, Market,Customer_Number)

)

Any help would be greatly appreciated.

Thanks!

Jen

1 Solution

Accepted Solutions

## Re: average, ignoring outliers, and current selection

Put the set modifier in the outer avg too.

avg({1<Customer_Number = ,Order_type = {Purchased}>}

aggr( ....etc

talk is cheap, supply exceeds demand
2 Replies

## Re: average, ignoring outliers, and current selection

Put the set modifier in the outer avg too.

avg({1<Customer_Number = ,Order_type = {Purchased}>}

aggr( ....etc

talk is cheap, supply exceeds demand
New Contributor III

## Re: average, ignoring outliers, and current selection

Thank you so much!  Very glad I asked!  Have a great day!