Max value of variables or an array

[#1]

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.

[sunny_talwar · Accepted solution]

Try RangeMax(vA, vB, vC)

[#3]

Thanks! Rangemax is what I am looking for!

[sunny_talwar]

Awesome

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

Best,

Sunny

[#5]

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

[sunny_talwar]

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))

[#7]

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!

[sunny_talwar]

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))

[#9]

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))

)))))

[SreeniJD]

Super Like Sunny