10 Replies Latest reply: Oct 13, 2015 11:41 AM by Sunny Talwar

# Max value of variables or an array

Hi, I am looking for a function that gives me the max value of a set of variables or an array, for example,

vA=1, vB=10, vC=100

unknown function(vA, vB, vC)=100

unknown function(  1,  10, 100)=100

Is there such a function in QlikView? Thanks.

• ###### Re: Max value of variables or an array

Try RangeMax(vA, vB, vC)

• ###### Re: Max value of variables or an array

Thanks! Rangemax is what I am looking for!

• ###### Re: Max value of variables or an array

Awesome

I am glad it worked out the way you wanted it.

Best,

Sunny

• ###### Re: Max value of variables or an array

Is there any way to get 2nd highest value? Thanks.

• ###### Re: Max value of variables or an array

May be something like this:

=RangeMax(If(RangeMax(0, 10, 100) = 0, 0, 0), If(RangeMax(0, 10, 100) = 10, 0, 10),  If(RangeMax(0, 10, 100) = 100, 0, 100))

or

=RangeMax(If(RangeMax(vA, vB, vC) = vA, 0, vA), If(RangeMax(vA, vB, vC) = vB, 0, vB),  If(RangeMax(vA, vB, vC) = vC, 0, vC))

• ###### Re: Max value of variables or an array

Sunny, this is wonderful! I am expanding the formula to the 3rd, the 4th... highest value but it seems to be much more complicated. Have you had a solution? Again, this is fantastic!

• ###### Re: Max value of variables or an array

How many values do you have to compare? 4? May be this:

=RangeMax(If(RangeMax(vA, vB, vC, vD) = vA, 0, vA), If(RangeMax(vA, vB, vC, vD) = vB, 0, vB),  If(RangeMax(vA, vB, vC, vD) = vC, 0, vC), If(RangeMax(vA, vB, vC, vD) = vD, 0, vD))

• ###### Re: Max value of variables or an array

Yes for 4 variables, the 2nd highest value would be the formula you just posted.
The 3rd highest value would be like below, or the 2nd lowest value of rangemin:

=rangemax(

IF(RangeMax(If(RangeMax(vA, vB, vC, vD) = vA, 0, vA), If(RangeMax(vA, vB, vC, vD) = vB, 0, vB),  If(RangeMax(vA, vB, vC, vD) = vC, 0, vC), IF(RangeMax(vA, vB, vC, vD) = vD, 0 ,vD))=vA,

RangeMax(If(RangeMax(vB, vC, vD) = vB, 0, vB),  If(RangeMax(vB, vC, vD) = vC, 0, vC), IF(RangeMax(vB, vC, vD) = vD, 0 ,vD)),

IF(RangeMax(If(RangeMax(vA, vB, vC, vD) = vA, 0, vA), If(RangeMax(vA, vB, vC, vD) = vB, 0, vB),  If(RangeMax(vA, vB, vC, vD) = vC, 0, vC), IF(RangeMax(vA, vB, vC, vD) = vD, 0 ,vD))=vB,

RangeMax(If(RangeMax(vA, vC, vD) = vA, 0, vA),  If(RangeMax(vA, vC, vD) = vC, 0, vC), IF(RangeMax(vA, vC, vD) = vD, 0 ,vD)),

IF(RangeMax(If(RangeMax(vA, vB, vC, vD) = vA, 0, vA), If(RangeMax(vA, vB, vC, vD) = vB, 0, vB),  If(RangeMax(vA, vB, vC, vD) = vC, 0, vC), IF(RangeMax(vA, vB, vC, vD) = vD, 0 ,vD))=vB,

RangeMax(If(RangeMax(vA, vB, vD) = vA, 0, vA),  If(RangeMax(vA, vB, vD) = vB, 0, vB), IF(RangeMax(vA, vB, vD) = vD, 0 ,vD)),

IF(RangeMax(If(RangeMax(vA, vB, vC, vD) = vA, 0, vA), If(RangeMax(vA, vB, vC, vD) = vB, 0, vB),  If(RangeMax(vA, vB, vC, vD) = vC, 0, vC), IF(RangeMax(vA, vB, vC, vD) = vD, 0 ,vD))=vD,

RangeMax(If(RangeMax(vA, vB, vC) = vA, 0, vA),  If(RangeMax(vA, vB, vC) = vB, 0, vB), IF(RangeMax(vA, vB, vC) = vC, 0 ,vC))

)))))

• ###### Re: Max value of variables or an array

I would probably take the RangeMin route:

=RangeMin(If(RangeMin(vA, vB, vC, vD) = vA, 99999999999, vA), If(RangeMin(vA, vB, vC, vD) = vB, 99999999999,vB),  If(RangeMin(vA, vB, vC, vD) = vC, 99999999999, vC), If(RangeMin(vA, vB, vC, vD) = vD, 99999999999, vD))

• ###### Re: Max value of variables or an array

Super Like Sunny