Calculate average on subset of records.

[#1]

Hi,

I'm new to Qlik and its' scripting language and I'm stuck on a seamingly simple task: To create an average, but only include the lowest 5% of the values.

The following is a javascript implementation I'm currently using:

// Sort the values

data = _.sortBy(data, alertness);

// Pick out the worst 5 percent of alertness values into new array

var worst5percent = _.first(data, Math.round(data.length * 0.05));

// Reduce the sum and divide by array length.

var pa5 = _.reduce(_.pluck(worst5percent, alertness), function (result, num) {

        return result + num;

    }, 0) / worst5percent.length;

Basically I need to sort, top and average, but I cannot seem to find a sort method. What can I do to achieve this result?

/Viktor

[JonnyPoole]

Hi Victor,  do you have some data to play with ?

In Sense the rank() and firstsortedvalue() functions can help with sorting and there is the ability to do nested aggregations using  aggr(). 

If you have a small sample of data and your desired result, kindly post and we'll get you started.

[#3]

Ok, it's pretty simple.

Let's say we have the following values and I want to calculate the average on the worst 10% of values.

Id, value

1, 55

2, 45

3, 48

4, 64

5, 65

6, 39

7, 42

8, 51

9, 61

10, 52

11, 53

12, 70

13, 68

14, 59

15, 55

16, 50

17, 49

18, 40

19, 51

20, 63

The desired result is then 39.5, since the worst 10% of values are 39 and 40. For 5% the result will be 39 and for 20% the result 41.5.

I hope this example made my question more clear.

/Viktor

[maxgro]

I think this expression (in a textbox or chart without dim)

avg(aggr(

  if(value < Fractile(TOTAL value, 0.2), value),

  Id))

replace 0.2 with 0.1, 0.05 or better with a variable