Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a set of salaries and would like to find the lowest/highest value below or over a fractile. The fractile function gives me the cut-off point but how do i get the highest or lowest value below or over the cut-off point?
Let's say I have the set below:
Payroll:
LOAD * INLINE [
Salary
3000
4000
5000
9000
11000
13000
17000
19000
22000
30000
45000
67000
];
fractile(total Salary,0.25) gives me the cut-off point 8000 but how do I get the value 5000? And fractile(total Salary,0.75) gives me the cut-off point 24000 but how do I get the value 30000?
Many thanks in advance,
Ola
Try with NODISTINCT keyword like:
=Max(If(Salary<=Aggr(NODISTINCT fractile(Salary,0.25),Dept),Salary ))
=Min(If(Salary>Aggr(NODISTINCT fractile(Salary,0.75),Dept),Salary ))
You can try something like:
=Max(If(Salary<fractile(total Salary,0.25) ,Salary)) // for immediate below value
=Min(If(Salary>fractile(total Salary,0.75) ,Salary)) // for immediate up value
Thank you tresesco! It works beautifully on the above dataset, however I have problems with the solution when adding a dimension to it. If I add a department dimension and try to do the same per department I don't get the desired result.
For example:
Payroll:
LOAD * INLINE [
Dept,Salary
DEPT01,3000
DEPT02,4000
DEPT02,5000
DEPT01,9000
DEPT02,11000
DEPT03,13000
DEPT01,17000
DEPT02,19000
DEPT03,22000
DEPT01,30000
DEPT02,45000
DEPT03,67000
];
Using fractile(total <Dept> Salary,0.25) and =Max(If(Salary<fractile(total <Dept> Salary,0.25) ,Salary)) as expressions in av pivot table produces this chart:
How would I go about getting the desired value per department?
Thanks in advance!
Ola
Try removing Salary as the dimension.
Unfortunately it gives the same result:
Ola
This?
=Max(If(Salary<fractile(total Salary,0.25) ,Salary))
Update:
The right one I believe:
=Max(If(Salary< Aggr(fractile(Salary,0.25),Dept) ,Salary),Dept)
Well, it doesn't take the department into account.
I'd like to get 13000 for DEPT03. Using TOTAL Salary without <Dept> makes fractile calulate on the whole set and not only per <Dept>. Is there someway to accomplish this?
Ola
Please check my updated expression:
=Max(If(Salary< Aggr(fractile(Salary,0.25),Dept) ,Salary),Dept)
Beautiful! It produces the desired result. I think I understand the expression but what does the last Dept do?
=Max(If(Salary< Aggr(fractile(Salary,0.25),Dept) ,Salary),Dept)
and when I change it to =Max(If(Salary <= Aggr(fractile(Salary,0.25),Dept) ,Salary),Dept) I expected DEPT02 to get the value 5000 but it doesn't?
And if I use it on =Min(If(Salary >= Aggr(fractile(Salary,0.75),Dept) ,Salary),Dept) it again produces nothing.
It seems that I do not understand the expression, could you please explain it so I can use it for the above mentioned cases?
Ola
Last Dept is a typo, and doesn't through an error because it takes as parameter to indicate rank. Please remove that.