Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Calculate average on subset of records.

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

Tags (2)
3 Replies
Employee
Employee

Re: Calculate average on subset of records.

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.

Not applicable

Re: Calculate average on subset of records.

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

MVP
MVP

Re: Calculate average on subset of records.

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