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
Ok, but why doesn't =Min(If(Salary > Aggr(fractile(Salary,0.75),Dept) ,Salary)) or even
=Max(If(Salary <= Aggr(fractile(Salary,0.25),Dept),Salary)) (note the <= ) work?
Thank you again for taking the time to educate me
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 ))
That did the trick!
Thank you so much, it really saved my weekend!
Ola