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: 
Amphan
Contributor III
Contributor III

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:

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

12 Replies
Amphan
Contributor III
Contributor III
Author

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

tresesco
MVP
MVP

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

Amphan
Contributor III
Contributor III
Author

That did the trick!

Thank you so much, it really saved my weekend!

Ola