Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jenstarr
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
jenstarr
New Contributor III

Re: average, ignoring outliers, and current selection

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

Community Browser