topic Fractile and average functions in the same expression in QlikView

Fractile and average functions in the same expression

Anonymous — Tue, 05 Apr 2011 11:11:32 GMT

Dear QV-users.

I hope someone of you out there can help me solving my problem with fractile and average functions in the same expression.

In my enclosed files I have 3 departments named A, B and C and I want to know what the average age is for the 20% oldest employees in each department and the average of all employees.

So far I have made an expression with the 80% fractile but I get an error when I try to combine my fractile function with avg.

In the expression my function is: =fractile(Age, 0.8) so It is possible to see the fractile for each department.

I really hope you can help me solving this.

Best regards,

Johnni

Fractile and average functions in the same expression

Anonymous — Wed, 06 Apr 2011 07:10:41 GMT

Can some of you guys help me?

Best regards,

Johnni

Fractile and average functions in the same expression

Wed, 06 Apr 2011 08:54:47 GMT

Hi,

try using this formula as an expression:

avg( {<employee = {"=rank(sum(-age))<=(count (total employee) *(3/4))"}>} age)

Where in field employee is an identified of employee and age is age:). This formula should calculate average age for lower (thats why there is minus sign in rank function) 75% fractile of employees sorted by age. If you need 80%, simply use 4/5 instead of 3/4 in my example.

Hope this helps

Tomas

Fractile and average functions in the same expression

Anonymous — Thu, 07 Apr 2011 10:08:53 GMT

Hi Tomas,

I tried your solution but it does not give the results I was looking for. I also did the calculations in Excel without expressions.

Maybe it is easier to sort the ages in each department and then calculate the average age on the 20% oldest. But I have no idea how to do that.

If you open the enclosed xlsx-file then you can see that I have grouped the names into three departsments. Each department has its own colour. I have made the sandbox in an easy way:

- 50 employees with unique names grouped into three departments. The average age of the ten oldest employees is 58,9 years.

- Dep. A with 10 employees marked red where the two last employees (light red) are in the 80 % fractile. Their average age is 59 years.

- Dep. B with 15 employees marked yellow where the three last employees (light yellow) are in the 80 % fractile. Their average age is 62,33 years.

- Dep. C with 25 employees marked green where the five last employees (light green) are in the 80 % fractile. Their average age is 55 years.

Johnni

Fractile and average functions in the same expression

Mon, 11 Apr 2011 09:04:32 GMT

Hi,

can you post your qvw file here? You did not write what's wrong with my formula - it does not work at all, or there are just different numbers - how they differ from your expectance?

Answer to this type of questions is usually rank function, you can make some experiments with it.

Otherwise you can prepare average age numbers in script, but you should take extra care when sorting values.

Tomas

Fractile and average functions in the same expression

Anonymous — Mon, 11 Apr 2011 09:27:29 GMT

Hi Thomas,

now I have posted the qvw file. I have made some calculation on 80% fractile with and without "Total" included in the expressions.

Furthermore I have made two expressions of your suggestion. The first where i changed 3/4 to 4/5 and the second where I did the same but also replaced "employee" with "Name".

Some of the results differ quite a bit from my expectations. The results should be (almost) as I wrote in my previous post.

Thank you for helping me.

Best regards

Johnni

Fractile and average functions in the same expression

Anonymous — Mon, 11 Apr 2011 09:28:40 GMT

Oops. Forgot to add the qvw file.

Fractile and average functions in the same expression

Fri, 22 Apr 2011 12:03:16 GMT

Hi,

it seems it takes top 80% of people disregarding department, but without "total" it does not work:(. Must admit, i did not figure it out it this certain pivot table.

But I think you are mixing two different things - fractile returns values - age, but rank function returns dimensions - employees. These are not exchangeable.

I did not help you, did I:(.

Tomas