Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Fractile and average functions in the same expression

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

7 Replies
Anonymous
Not applicable
Author

Can some of you guys help me?

Best regards,

Johnni

Not applicable
Author

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

Anonymous
Not applicable
Author

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

Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

Oops. Forgot to add the qvw file.

Not applicable
Author

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