2 Replies Latest reply: Dec 5, 2013 2:00 PM by Jen Starr RSS

    average, ignoring outliers, and current selection

    Jen Starr

      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