Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
May be this
RangeMax(Sum(Price), Sum(Price1), Sum(Price2)....)
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))
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?
I am sure there must be a way, would you be able to share some data to see what we are looking at?
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!
Awesome, I am glad you managed to solve this