Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

'Match' function in Excel resemble function in Qlikview?

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.

6 Replies
Highlighted

Re: 'Match' function in Excel resemble function in Qlikview?

May be this

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

Highlighted

Re: 'Match' function in Excel resemble function in Qlikview?

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

Highlighted
Contributor II
Contributor II

Re: 'Match' function in Excel resemble function in Qlikview?

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?

Highlighted

Re: 'Match' function in Excel resemble function in Qlikview?

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

Highlighted
Contributor II
Contributor II

Re: 'Match' function in Excel resemble function in Qlikview?

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!

Highlighted

Re: 'Match' function in Excel resemble function in Qlikview?

Awesome, I am glad you managed to solve this