Second Max Value in Column Range (Straight Table)

[Anonymous]

Hi Guys,

Can you please advise me how can I get second or third max value in column range?

Col_1	Col_2	Col_3	Col_4	Col_5
10	20	30	40	50

Currently I am using RangeMax & RangeMin to get Max or Min value. But I get to get 2 or 3 from the range.

Kind regards,

Farrukh

[settu_periasamy]

Hi,

Col_1, Col_2.. etc  are Label of expressions, right? Then try like this..(for 3rd Max)

Pick(Match(

RangeMax(Col_1,Col_2,Col_3,Col_4,Col_5),

    Col_1,Col_2,Col_3,Col_4,Col_5)-2,

Col_1,Col_2,Col_3,Col_4,Col_5)

[Anil_Babu_Samineni]

I believe, Pick(Match(... Won't support expression like you said. I think we can create Key for all columns and we can use same?

Thread Owner - What is the output you want to get from given input?

[Anonymous]

Hi Settu,

It did not work

Farrukh

[Anonymous]

Hi Anil,

I have 5 columns where the numerical data exist.

Col_1	Col_2	Col_3	Col_4	Col_5
10	20	30	40	50

I want to pick nth value as per result, it could be first value , second value, or any from above 5 columns.

Kind regards,

Farrukh

[Anonymous]

Hi Anil,

I have written the formula in excel and its giving me correct result.

I am looking to write an expression in Qlikview.

Index Created: INDEX($BL3:$BP3

Max Value as per Input ((LARGE($BL3:$BP3,1)

And then Match

=IF(DN3>0,

IF(ISNUMBER(INDEX($BL3:$BP3,MATCH(IF(LARGE($BL3:$BP3,1)=BO3,BO3,0),$BL3:$BP3,0)))=TRUE,"1H",

IF(ISNUMBER(INDEX($BL3:$BP3,MATCH(IF(LARGE($BL3:$BP3,2)=BO3,BO3,0),$BL3:$BP3,0)))=TRUE,"2H",

IF(ISNUMBER(INDEX($BL3:$BP3,MATCH(IF(LARGE($BL3:$BP3,3)=BO3,BO3,0),$BL3:$BP3,0)))=TRUE,"3H",

IF(ISNUMBER(INDEX($BL3:$BP3,MATCH(IF(LARGE($BL3:$BP3,4)=BO3,BO3,0),$BL3:$BP3,0)))=TRUE,"2L",

IF(ISNUMBER(INDEX($BL3:$BP3,MATCH(IF(LARGE($BL3:$BP3,5)=BO3,BO3,0),$BL3:$BP3,0)))=TRUE,"1L"))))),"")

Can you please advise me related to this? How can I achieve in Qlikvew.

Kind regards,

Farrukh

[Anil_Babu_Samineni]

Please share Expected result set?

[Anonymous]

Hi Anil

Please see the attached excel sheet where the excel formula built-in.

Regards,

Farrukh

[Anonymous]

Hi Settu,

Please see the attached excel sheet where the excel formula built-in.

Can you please advise me how to get the similar result in excel.

Regards,

Farrukh

[Anil_Babu_Samineni]

What you got, When you use this?

If(Sum(Seq_Count_1)>0,

If(IsNum(Index(NoOfColumns(TOTAL), Match(If(RangeMax(RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5),1)=Sum(Seq_Cum_Count_1), Sum(Seq_Cum_Count_1), 0), RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5), 0)))=True(), '1H',

If(IsNum(Index(NoOfColumns(TOTAL), Match(If(RangeMax(RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5),2)=Sum(Seq_Cum_Count_1), Sum(Seq_Cum_Count_1), 0), RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5), 0)))=True(), '2H',

If(IsNum(Index(NoOfColumns(TOTAL), Match(If(RangeMax(RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5),3)=Sum(Seq_Cum_Count_1), Sum(Seq_Cum_Count_1), 0), RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5), 0)))=True(), '3H',

If(IsNum(Index(NoOfColumns(TOTAL), Match(If(RangeMax(RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5),4)=Sum(Seq_Cum_Count_1), Sum(Seq_Cum_Count_1), 0), RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5), 0)))=True(), '2L',

If(IsNum(Index(NoOfColumns(TOTAL), Match(If(RangeMax(RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5),5)=Sum(Seq_Cum_Count_1), Sum(Seq_Cum_Count_1), 0), RangeSum(Seq_Cum_Count_1, Seq_Cum_Count_2, Seq_Cum_Count_3, Seq_Cum_Count_4, Seq_Cum_Count_5), 0)))=True(), '1L'))))), '')