topic Re: Finding lowest actual value which is >= AVG in QlikView

Finding lowest actual value which is >= AVG

Mon, 17 Dec 2012 22:45:11 GMT

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

Re: Finding lowest actual value which is >= AVG

Gysbert_Wassenaar — Tue, 18 Dec 2012 12:09:15 GMT

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.

Re: Finding lowest actual value which is >= AVG

hic — Tue, 18 Dec 2012 14:44:51 GMT

The following formula correctly calculates the >Avg problem:

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

HIC

Re: Finding lowest actual value which is >= AVG

Tue, 18 Dec 2012 15:21:33 GMT

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

Re: Finding lowest actual value which is >= AVG

Tue, 18 Dec 2012 15:29:23 GMT

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