topic Re: Max value below fractile in QlikView

Max value below fractile

Amphan — Thu, 10 Apr 2014 09:00:23 GMT

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

Re: Max value below fractile

tresB — Thu, 10 Apr 2014 09:07:22 GMT

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

Amphan — Thu, 10 Apr 2014 10:31:30 GMT

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

Re: Max value below fractile

tresB — Thu, 10 Apr 2014 10:40:58 GMT

Try removing Salary as the dimension.

Re: Max value below fractile

Amphan — Thu, 10 Apr 2014 10:47:01 GMT

Unfortunately it gives the same result:

Ola

Re: Max value below fractile

tresB — Thu, 10 Apr 2014 10:57:24 GMT

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

Amphan — Thu, 10 Apr 2014 11:07:52 GMT

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

tresB — Thu, 10 Apr 2014 11:09:22 GMT

Please check my updated expression:

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

Re: Max value below fractile

Amphan — Thu, 10 Apr 2014 11:36:55 GMT

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

tresB — Thu, 10 Apr 2014 11:41:28 GMT

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

Amphan — Thu, 10 Apr 2014 11:48:53 GMT

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

tresB — Fri, 11 Apr 2014 05:25:17 GMT

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

Amphan — Fri, 11 Apr 2014 12:45:50 GMT

That did the trick!

Thank you so much, it really saved my weekend!

Ola