Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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'))))), '')
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)
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?
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)
Its a Select_F1 is the field needs to fix.
Farrukh
Yes Settu.
Please help me out for this. Kind regards, Farrukh
I tried this, but not getting the expected output. I will look into this again.
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)))
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.
CDF1 | CDF2 | CDF3 | CDF4 | CDF5 | Select_F1 | Select_F2 | Select_F3 | Select_F4 | Select_F5 |
0 | 0 | 2 | 0 | 0 | First Highest | ||||
1 | 5 | 3 | 4 | 2 | Lowest | First Highest | Third Highest | Second Highest | 2nd Lowest |
1 | 0 | 2 | 0 | 0 | Second Highest | First 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)