Second Minimum Value from a list of Values

[mrthomasshelby]

Hi,

We can extract the minimum value from amongst multiple values by using RangeMin(exp1,exp2,...). Is there anyway to extract the 2nd Minimum Value from multiple values?

Let's consider this for an example:

M1	M2	M3	M4	M5	M6	M7	M8
15	16	17	18	19	21	26	31

RangeMin(M1,M2,M3,M4,M5,M6,M7,M8) will return 15. What I would like is to extract the 2nd Minimum value from the list. How to go about it given that there isn't a rank argument available in the RangeMin function? Attached sample app with 3 measures M1,M2 and M3. Please help calculate the 2nd and 3rd minimum columns.

Thanks in advance.

@sunny_talwar @tresesco @MarcoWedel 

[anat]

can u try using Min function : Min(UnitSales,2) to get second min value

or try using firstsortedvalue

[mrthomasshelby]

Hi  Anat, unfortunately I cannot use Min or Firstsortedvalue as each of M1,  M2 etc. are all different measures calculated using different formulae.

[Andrei_Cusnir]

Hello,

You can try using the Pick() function [1].

Expression:

Pick(2, Sum(Value), Min(Value), Max(Value), Avg(Value))

General:

Pick(2, M1, M2, M3 ...)

The first argument is the index of which expression you would like to return.

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members. 
 

---

[1] https://help.qlik.com/en-US/sense/November2021/Subsystems/Hub/Content/Sense_Hub/Scripting/Conditiona...

[mrthomasshelby]

Hi Andrei,

This will only return the 2nd value in the list and not the 2nd Minimum Value from the list. I want to extract the 2nd Minimum value amongst M1,M2,M3,M4....

In my example above the 2nd Minimum value amongst M1,M2,M3,M4,M5,M6,M7,M8 is 16 (M2).

[Andrei_Cusnir]

Hello,

Thanks for the clarification again, as I have missed this detail from the description. The closest that I was able to get to your expected outcome is by following these steps:

1. In the Data load editor, I have generated a new table with the expressions' values already calculated there:

GenerateExpressionsValues:
Load

     Sum(Value) as SumExpression,
     Avg(Value) as AvgExpression,
     Min(Value) as MinExpression,
     Max(Value) as MaxExpression

Resident Data;

Let E1 = Peek('SumExpression');
Let E2 = Peek('AvgExpression');
Let E3 = Peek('MinExpression');
Let E4 = Peek('MaxExpression');

Expressions:
Load * Inline [
    E
    $(E1)
    $(E2)
    $(E3)
    $(E4)
];

Drop Table GenerateExpressionsValues;

2. After that I had the table:

3. Now I have created a new KPI and you can use the expression: =Concat(E,'-', E)

As you can see, this has created a string with all the values sorted in Ascending order.

4. Now if you also use the function SubField() as in expression: =SubField(Concat(E,'-', E), '-', 2), you will get:

As you can see, we got the 2nd min value by splitting the string into parts based on delimiter '-' and then selecting the 2nd part.

I hope that this information was helpful.

[tresesco]

Try something like (UI):

Max(valueList( $(=$(M1)) ,$(=$(M2)) ,$(=$(M3)),$(=$(M4)),$(=$(M5)),$(=$(M6)),$(=$(M7)),$(=$(M8)) ), 2)

Or

Max(valueList( M1,M2, M3, M4, M5, M6, M7, M8 ), 2)

[mrthomasshelby]

Hi tresesco,

When I try to use this, I get an expression error saying 'All parameters to ValueList must be constant'. For further clarity, M1,M2,M3... are all Aggr() of a variable over one dimension.

[mrthomasshelby]

Thank you Andrei for your efforts. I was hoping for something in the front end part but I guess I can maybe do this as a last resort.

[tresesco]

In that case, I guess you have to try something in line with the first option I showed, i.e - with dollar expansion. If that doesn't help, try to share an app with sample data to work on.