topic Finding MIN of Calculation Across Chart in QlikView

Finding MIN of Calculation Across Chart

jlassell — Wed, 06 Jan 2010 23:36:36 GMT

I am trying to create an expression for the "min cost/day" column below:

product	sum(cost)	sum(days)	cost/day	min cost/day
DILAUDID-5	2,564.99	468	5.48	5.48
HYDROMORPHONE HCL	131.11	16	8.19	5.48
OPANA ER	233.34	21	11.11	5.48
OXYCONTIN	964.81	73	13.22	5.48
KADIAN	2,752.38	194	14.19	5.48

I need to use that value in other calculations for the higher cost/day products -- ie. potential savings if you had used DILAUDID-5 instead of OPANA ER.

I would have thought I could use something like: min( aggr(sum(cost)/sum(days), product) )

but that doesn't seem to work.

Any ideas?

Finding MIN of Calculation Across Chart

Anonymous — Thu, 07 Jan 2010 03:28:07 GMT

There are probably better ways to go about this but if the table is always sorted with the lowest cost/day on top you can use the following expression to pull the value to the 4th expression:

rangemin(above(Sum(Cost)/Sum(Days),0,Rowno()))

Finding MIN of Calculation Across Chart

jlassell — Thu, 07 Jan 2010 23:23:24 GMT

If I add this expression to the chart I am no longer able to sort the column that has the Cost/Day. Even if I set the Sort Orders the rows don't show up sorted anymore.

Finding MIN of Calculation Across Chart

jlassell — Thu, 07 Jan 2010 23:35:44 GMT

I was able to accomplish what I needed with nested aggr functions.

aggr(nodistinct min( aggr(sum(cost/day), DimX)) , DimY)

Thanks,