12 Replies Latest reply: Apr 11, 2014 8:45 AM by Ola Amphan

# Max value below fractile

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:

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?

Ola

• ###### Re: Max value below fractile

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

• ###### Re: Max value below fractile

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:

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?

Ola

• ###### Re: Max value below fractile

Try removing Salary as the dimension.

• ###### Re: Max value below fractile

Unfortunately it gives the same result:

Ola

• ###### Re: Max value below fractile

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)

• ###### Re: Max value below fractile

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

• ###### Re: Max value below fractile

=Max(If(Salary< Aggr(fractile(Salary,0.25),Dept) ,Salary),Dept)

• ###### Re: Max value below fractile

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

• ###### Re: Max value below fractile

Last Dept  is a typo, and doesn't through an error because it takes as parameter to indicate rank. Please remove that.

• ###### Re: Max value below fractile

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

• ###### Re: Max value below fractile

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 ))

• ###### Re: Max value below fractile

That did the trick!

Thank you so much, it really saved my weekend!

Ola