topic Max Value through firstsortedvalue function... in QlikView

Max Value through firstsortedvalue function...

Wed, 25 Dec 2013 04:57:42 GMT

Hello Friends,

I have data as depicted in the following sample table:

For minimum of rate I have to display the maximum of purchase order. Thus for the above sample my result should be:

I tried using: FirstSortedValue(PURCHASE_ORDER,RATE).

It works when there is just 1 occurrence of minimum rate, but otherwise it results in null value. ( I have to accomplish this in front end, so no reload is possible).

Please suggest a solution.

Re: Max Value through firstsortedvalue function...

Wed, 25 Dec 2013 06:17:04 GMT

Hi Friends,

I figured out the answer by hit and trial. For those who are interested:

FirstSortedValue(aggr(max(PURCHASE_ORDER),RATE), aggr(min(RATE),RATE))

Re: Max Value through firstsortedvalue function...

Wed, 25 Dec 2013 07:24:08 GMT

Added note:

If you have multiple material in he table then change the formula to:

FirstSortedValue(aggr(max(PURCHASE_ORDER),RATE,MATERIAL), aggr(min(RATE),CAL_RATE_P,MATERIAL)))