'Match' function in Excel resemble function in Qlikview?

[rupalighaste]

Hi,

In a Pivot, I've following expressions:

Sum(Price), Sum(Price1), Sum(Price2), Sum(Price3),Sum(Price4) ,Sum(Price5),Sum(Price6),Sum(Price7)

How can I find the largest value from (Sum(Price1), Sum(Price2)...., Sum(Price7)) whose value is <=Sum(Price). Here I need that value as well as column name like Price1, Price2 etc which is fulfilling the condition.

[sunny_talwar]

May be this

RangeMax(Sum(Price), Sum(Price1), Sum(Price2)....)

[sunny_talwar]

Or this

RangeMax(

If(Sum(Price1) <= Sum(Price), Sum(Price1), 0),

If(Sum(Price2) <= Sum(Price), Sum(Price2), 0),

If(Sum(Price3) <= Sum(Price), Sum(Price3), 0),

If(Sum(Price4) <= Sum(Price), Sum(Price4), 0),

If(Sum(Price5) <= Sum(Price), Sum(Price5), 0),

If(Sum(Price6) <= Sum(Price), Sum(Price6), 0),

If(Sum(Price7) <= Sum(Price), Sum(Price7), 0))

[rupalighaste]

Hi, its giving me second highest value however is there any way to find column name/position also.

like RangeMax(Sum(Price1),Sum(Price2),Sum(Price3) gives me actual value but how to get column name/position for that associated value?

[sunny_talwar]

I am sure there must be a way, would you be able to share some data to see what we are looking at?

[rupalighaste]

Its done. I just tried to match the Rangemax() value with all price columns to find the position/name of the column. But I didn't find any built in function for this in Qlikview . Anyways I got my solution thank you so much for your help on this as with your provided solution I was able to continue. Thanks!

[sunny_talwar]

Awesome, I am glad you managed to solve this