Finding lowest actual value which is >= AVG

[#1]

I have a simple sheet of order data, including an order number, order date, partno, and order days (time it took to fill order).  For each Partno, I display the average numbr of OrderDays and the 90th Percentile for OrderDays.  However, I need to also display the lowest OrderDays which is greater than or equal to the Average.  I have to do similar for lowest OrderDays >= 90th Percentile.  I have tried all kinds of combinations of expressions, sets, conditions, etc. but cannot get an answer.  The calculation should always reflect the user's current selection.

QVW is attached with the data, charts, and desired results.  We've tried consultants and searching forums without luck... this seems like it should be so simple in QV.  Thanks in advance for any assistance.

Rich

[Gysbert_Wassenaar]

The only way I found is to calculate the average and 90th percentile in the script. As long as these kind of metrics don't change too often this is a practical solution. If necessary you could easily generate more x-th percentiles. Once these have been calculated in the script the expressions in the charts are fairly straightforward. See attached qvw.

[hic · Accepted solution]

The following formula correctly calculates the >Avg problem:

=Min(aggr(if(Min(OrderDays)>avg(total <PartNo> OrderDays),Min(OrderDays)), PartNo, OrderDays ))

HIC

[#4]

Thanks for the info, but the solution has to update real-time based on the user's selection.

[#5]

Thank you Henric, that worked fine for the Average. (better yet, I think I understand it...). I used the same approach using Fractile instead of Average for the 90th Percentile solution and got it to work.  It always returned null initially, but when I removed all comments from before & after the expression it worked fine.

=Min(aggr(if(Min(OrderDays)>fractile(total <PartNo> OrderDays,0.9), Min(OrderDays)), PartNo, OrderDays ))

Thank you again,

Rich