Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Second Max Value in Column Range (Straight Table)

Hi Guys,

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

   

Col_1Col_2Col_3Col_4Col_5
1020304050

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

32 Replies
Anonymous
Not applicable
Author

Hi Anil,

The above expression did not work for me.

I have attached the QVW and the (Example of Excel Sheet).

Can you please advise me related to this?

Kind regards,

Farrukh

Anil_Babu_Samineni

Will test tomorrow, For a while can you set this? This will work until unless condition not fulfill

If(Sum(CDC1)>0,

If(IsNum(Index(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), Match(If(RangeMax(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5),1)=Sum(CDC1), Sum(CDC1), 0),

RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), 0)))=True(), '1H',

If(IsNum(Index(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), Match(If(RangeMax(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5),2)=Sum(CDC1), Sum(CDC1), 0),

RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), 0)))=True(), '2H',

If(IsNum(Index(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), Match(If(RangeMax(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5),3)=Sum(CDC1), Sum(CDC1), 0),

RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), 0)))=True(), '3H',

If(IsNum(Index(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), Match(If(RangeMax(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5),4)=Sum(CDC1), Sum(CDC1), 0),

RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), 0)))=True(), '2L',

If(IsNum(Index(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), Match(If(RangeMax(RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5),5)=Sum(CDC1), Sum(CDC1), 0),

RangeSum(CDC1, CDC2, CDC3, CDC4, CDC5), 0)))=True(), '1L'))))), '')

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

Hi Anil,

Hi have edited the above expression but still did not get luck.

I highlighted below expression where I think I am having an issue. Please see the previously attached qvw file where I have but expression for CDF1,2.... and further.

Please help me out for this. Kind regards, Farrukh

=
If(CDC1>0,

If(IsNum(Index(RangeSum(
Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
),
Match(If(RangeMax(RangeSum(
Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
),1)=
Sum(Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL)))), Sum(Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL)))), 0),

RangeSum(
Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
), 0)))=
True(), '1H',0),0)

settu_periasamy
Master III
Master III

Hi Farrukh,

Which field needs to look? CDF1 to CDF15  or Select_F1 to Select_F5?

Can you download the chart in excel (from attached QV) and give the expected output with some colors?

Anonymous
Not applicable
Author

Hi Settu,

Please see the attached QV file and Excel Sheet, (Excel Sheet has correct formula and I am trying to do the same result in Qlikview)

=
If(CDC3>0,

If(IsNum(Index(ValueList(
Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
),
Match(If(Max(ValueList(
Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
),1)=
Sum(Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL)))), Sum(Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL)))), 0),

ValueList(
Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
), 0)))=
True(), '1H',0),0)

Anonymous
Not applicable
Author

Its a Select_F1 is the field needs to fix.

Farrukh

Anonymous
Not applicable
Author

Yes Settu.

Please help me out for this. Kind regards, Farrukh

settu_periasamy
Master III
Master III

I tried this, but not getting the expected output. I will look into this again.

Anil_Babu_Samineni

Does this correct?

If(CDC1>0,
If(IsNum(Index(
RangeSum(
Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL
)))

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

Hi Anil,

Please see the attached QV file and Excel Sheet, (Excel Sheet has correct formula and I am trying to do the same result in Qlikview)

The column Im tying to fix is "Select_F1" as shown in Excel file.

In QVW File CDF1 is a cumulative sum of CDC1. the logic im trying to build is if CDC1 > 0 then find the value as per (CDF1, CDF2, CDF3, CDF4 and CDF5) as shown in below table.

   

CDF1CDF2CDF3CDF4CDF5Select_F1Select_F2Select_F3Select_F4Select_F5
00200First Highest
15342Lowest First HighestThird Highest Second Highest2nd Lowest
10200 Second HighestFirst Highest

Currently using the below expression.

=
If(CDC1>0,

If(IsNum(Index(ValueList(
Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
),
Match(If(Max(ValueList(
Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
),1)=
Sum(Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL)))), Sum(Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL)))), 0),

ValueList(
Rangesum(Above(TOTAL Sum(CDC1),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC2),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC3),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC4),0,RowNo(TOTAL))),
Rangesum(Above(TOTAL Sum(CDC5),0,RowNo(TOTAL)))
), 0)))=
True(), '1H',0),0)