average, ignoring outliers, and current selection

Anonymous — Thu, 05 Dec 2013 18:49:47 GMT

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

Re: average, ignoring outliers, and current selection

Gysbert_Wassenaar — Thu, 05 Dec 2013 18:53:33 GMT

Put the set modifier in the outer avg too.

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

aggr( ....etc

Re: average, ignoring outliers, and current selection

Anonymous — Thu, 05 Dec 2013 19:00:00 GMT

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

topic Re: average, ignoring outliers, and current selection in QlikView

average, ignoring outliers, and current selection

Re: average, ignoring outliers, and current selection

Re: average, ignoring outliers, and current selection