topic Re: Second Minimum Value from a list of Values in App Development

Second Minimum Value from a list of Values

mrthomasshelby — Thu, 24 Feb 2022 06:01:51 GMT

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 @tresB @MarcoWedel

Re: Second Minimum Value from a list of Values

anat — Wed, 23 Feb 2022 08:11:47 GMT

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

or try using firstsortedvalue

Re: Second Minimum Value from a list of Values

mrthomasshelby — Wed, 23 Feb 2022 08:13:56 GMT

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

Re: Second Minimum Value from a list of Values

Andrei_Cusnir — Wed, 23 Feb 2022 08:25:46 GMT

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/ConditionalFunctions/pick.htm

Re: Second Minimum Value from a list of Values

mrthomasshelby — Wed, 23 Feb 2022 08:31:48 GMT

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

Re: Second Minimum Value from a list of Values

Andrei_Cusnir — Wed, 23 Feb 2022 09:52:09 GMT

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.

Re: Second Minimum Value from a list of Values

tresB — Wed, 23 Feb 2022 10:25:46 GMT

Try something like (UI):

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

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

Re: Second Minimum Value from a list of Values

mrthomasshelby — Wed, 23 Feb 2022 11:01:52 GMT

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.

Re: Second Minimum Value from a list of Values

mrthomasshelby — Wed, 23 Feb 2022 11:03:18 GMT

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.

Re: Second Minimum Value from a list of Values

tresB — Wed, 23 Feb 2022 11:05:53 GMT

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.

Re: Second Minimum Value from a list of Values

mrthomasshelby — Wed, 23 Feb 2022 13:08:17 GMT

Here's a sample app. Need to calculate 2nd & 3rd Minimums of var1, var2 and var3 which are variables.

Re: Second Minimum Value from a list of Values

tresB — Wed, 23 Feb 2022 13:39:25 GMT

If you have multiple rows, then this won't work as expected. If you reduce your row to one, you get it using simpler expression:

For multiple rows, I guess you would need bit more complex expression - this in combination with pick(... to make it row-wise calculation

Re: Second Minimum Value from a list of Values

MarcoWedel — Thu, 24 Feb 2022 22:13:03 GMT

Maybe something like

=RangeMin( If(M1>RangeMin(M1,M2,M3,M4,M5),M1), If(M2>RangeMin(M1,M2,M3,M4,M5),M2), If(M3>RangeMin(M1,M2,M3,M4,M5),M3), If(M4>RangeMin(M1,M2,M3,M4,M5),M4), If(M5>RangeMin(M1,M2,M3,M4,M5),M5) )