topic Re: Calculate average on subset of records. in App Development

Calculate average on subset of records.

Wed, 01 Oct 2014 12:48:01 GMT

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

Re: Calculate average on subset of records.

JonnyPoole — Wed, 01 Oct 2014 17:35:23 GMT

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.

Re: Calculate average on subset of records.

Thu, 02 Oct 2014 06:56:08 GMT

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

Re: Calculate average on subset of records.

maxgro — Thu, 02 Oct 2014 07:37:21 GMT

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