Max value below fractile

[Amphan]

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

[tresesco]

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

[Amphan]

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

[tresesco]

Try removing Salary as the dimension.

[Amphan]

Unfortunately it gives the same result:

Ola

[tresesco]

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)

[Amphan]

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

[tresesco]

Please check my updated expression:

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

[Amphan]

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

[tresesco]

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