Find nth smallest value

[irura]

Hi All,

I would like to ask how can QlikSense return the nth smallest value for an array. 

My data set is:

Value	Count
4400	1
4550	1
4900	3
5000	2
5100	1
5200	2
5400	1
5500	4
5550	1
5600	2
5700	4
5800	1
5950	1
6000	4
6092	1
6100	5

For example, I want the 4th smallest number, which is 4900, but Qlik Sense function Min(Value, 4) would give me result 5000. How can I solve this? Thank you.

[tresesco]

What is the logic that makes 4900 the 4th smallest?

[irura]

its count is 3

[tresesco]

Count wise - you have 1,2,3,4,5.....then how come count 3 is 4th? Sorry, a bit more explanation would be helpful.

[irura]

No, the array should be like 4400, 4550, 4900, 4900, 4900, 5000, 5000, 5100...... so 4th smallest is 4900

[tresesco]

Ok,,got it. Where are you going to display this 4th smallest value..in kpi object?

[ArnadoSandoval]

Hi Irura,

It is a very interesting challenge, I do not know about any function that could directly return the result you expect, but with some transformations, it will be possible; you initially presented a frequencies array, then you changed it to an array with the frequency removed (expanded); I worked with the frequencies array while preparing this reply.

I will be using Interval (mathematics) in my solution; first of all, I added two new columns defining the start-end points of each interval:

Your data with intervals

Value	Count	a	b
4400	1	1	1
4450	1	2	2
4900	3	3	5
5000	2	6	7
...	...	...	...

So, when you ask, who is the n-th smallest value? or the 4th in your example, the result is in the interval between 3 and 4, e.g. 4900; while if we ask for the 7th smallest value, the interval [6, 7] contains the answer, e.g. 5000.

Creating the Interval columns in Qlik's data load script:

The RangeSum function helps with finding your frequency array intervals, note, the frequency array must be ordered by the Value column; this community post was of great help:  Running Sum on Load Script 

WORK:
Load *, 
     Num(RangeSum(peek(I_b), Count))             as I_b,
     Num(RangeSum(peek(I_b), Count)) - Count + 1 as I_a;
Load Value,
     Count
Resident Frequencies_array
Order by Value;

The script above generates each value interval as shown:

Finding the interval for the n-th smallest value:

The figure below shows how to find the n-th smallest value within the intervals.

Including these formulas in the Qlik script is simple; we want to return 1 when the condition is true, 0 otherwise, just like this:

WORK:
Load *, 
     if($(vPosition)>=I_a, 1, 0) as Inside_a,
     if($(vPosition)<=I_b, 1, 0) as Inside_b;
Load *, 
     Num(RangeSum(peek(I_b), Count))             as I_b,
     Num(RangeSum(peek(I_b), Count)) - Count + 1 as I_a;
Load Value,
     Count
Resident Frequencies_array
Order by Value;

The variable vPosition plays a very important role in this solution, as it allows us to change the n-th position we want to find; the WORK table with these Inside columns now look like this:

To be continued ...

[ArnadoSandoval]

Hi Irura,

It is a very interesting challenge, I do not know about any function that could directly return the result you expect, but with some transformations, it will be possible; you initially presented a frequencies array, then you changed it to an array with the frequency removed (expanded); I worked with the frequencies array while preparing this reply.

I will be using Interval (mathematics) in my solution; first of all, I added two new columns defining the start-end points of each interval:

Your data with intervals

Value	Count	a	b
4400	1	1	1
4450	1	2	2
4900	3	3	5
5000	2	6	7
...	...	...	...

So, when you ask, who is the n-th smallest value? or the 4th in your example, the result is in the interval between 3 and 4, e.g. 4900; while if we ask for the 7th smallest value, the interval [6, 7] contains the answer, e.g. 5000.

Creating the Interval columns in Qlik's data load script:

The RangeSum function helps with finding your frequency array intervals, note, the frequency array must be ordered by the Value column; this community post was of great help:  Running Sum on Load Script 

WORK:
Load *, 
     Num(RangeSum(peek(I_b), Count))             as I_b,
     Num(RangeSum(peek(I_b), Count)) - Count + 1 as I_a;
Load Value,
     Count
Resident Frequencies_array
Order by Value;

The script above generates each value interval as shown:

Finding the interval for the n-th smallest value:

The figure below shows how to find the n-th smallest value within the intervals.

Including these formulas in the Qlik script is simple; we want to return 1 when the condition is true, 0 otherwise, just like this:

WORK:
Load *, 
     if($(vPosition)>=I_a, 1, 0) as Inside_a,
     if($(vPosition)<=I_b, 1, 0) as Inside_b;
Load *, 
     Num(RangeSum(peek(I_b), Count))             as I_b,
     Num(RangeSum(peek(I_b), Count)) - Count + 1 as I_a;
Load Value,
     Count
Resident Frequencies_array
Order by Value;

The variable vPosition plays a very important role in this solution, as it allows us to change the n-th position we want to find; the WORK table with these Inside columns now look like this:

The columns (In a) and (In b) shows the evaluation of the conditions for the requested position, both of them returns 1 when we are inside of the range for the position 15 (our example). The final implementation does not require you to create the columns (In a) and (In b), they are here to illustrate its logic.

We know that two conditions linked by the logical AND is equivalent to multiplying their results, so when we multiply (In a) * (In b) we get the result we want, and only the row with the figure 5500 (the 15th slowest value) contains one which is true. If our condition is (In a) * (In b) * Value, the result column contains 5500, the solution we want.

The reply editor is giving me a lot of troubles, so I am having difficulties writing my reply, I will keep trying !!!

Should we use the if statements or convert them to a more mathematical form:

I used "if($(vPosition)<=I_a, 1, 0)" above, which is using a full featured if statement, but it is equivalent to this expression using the SIGN function:  Sign(1 + Sign($(vPosition) - I_a)) ; while the condition for the interval ending point b, become: Sign(1 - Sign( $(vPosition) - I_b));  so we could use these expression returning the n-th lowest value:

Sign(1 + Sign($(vPosition) - I_a)) * Sign(1 - Sign( $(vPosition) - I_b)) * Value

Solution:

1. Your load script should create the interval's start and ending points columns, e.g: columns I_a and I_b.

Let Position = 0;
WORK:
Load *, 
     Num(RangeSum(peek(I_b), Count))             as I_b,
     Num(RangeSum(peek(I_b), Count)) - Count + 1 as I_a;
Load Value,
     Count
Resident Frequencies_array
Order by Value;

2. After running the Load Script, create a new sheet.

3. Add a text input field: Custom Objects --> About Qlik Dashboard bundle --> Variable Input

4.  Assign the variable vPosition to your variable input.

5. Add a straight table showing the Frequency Array (optional)

6. Add another straight table, pick Value as its first column, modify the Chart-Type to Treemap, modify its Value column to use the  n-th lowest value expression (shown above), change its label to anything you want.

7. Add another column to the Treemap chart, its expression is also the n-th lowest value expression, change its label to anything you want.

8. Enter different positions in the Position textbox while testing your solution.

Hope this helps

Arnaldo Sandoval