Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
summerrain
Contributor III
Contributor III

Calculation of Percentiles (fractile within fractile)

My data set is:

IDPositionSalary
1C100
2C200
3C300
4B400
5B500
6B600
7B700
8A800
9A900
10A1000

My first task is to calculate 90th percentile per Position. That I can achieve easy with expression Fractile(Salary, .9)

pct.JPG


My second task is what got me totally stuck and out of ideas I have to implement ability for user to exclude salaries above or equal 90th percentile and to recalculate 90th percentile again...per position.

So what I have achieved is:

1. Created load script

HideAbove90thPercentile:

Load * Inline

[HideAbove90thPercentile

"Hide Above 90th Percentile"

];


2. Created a listbox with checkbox type:

hid.JPG

3.Created variable v90:

=Fractile(Salary, .9)

4. In my straight table Percentile per Position created expression:

=if(GetSelectedCount([HideAbove90thPercentile])=0,

Fractile(Salary, .9),

if(GetSelectedCount([HideAbove90thPercentile])=1,

Fractile({$<Salary ={"<=$(v90)"}>}Salary, .9)

))

So the result I get is incorrect. Variable v90 gets calculated before chart and it's value is for all the positions. So salary in the expression above is compared not by 90th percentile per position, but whole set of positions. Is it possible to calculate it per row level/single position? That it would show correct 90th Percentile per position in the chart below. Now it shows incorrect for B and C. We can see correct values only if we select single positions.

Workaround to create variables for each of positions wouldn't work since in a real life application I have hundreds of different positions.

Result:

snip.JPG

Should be:

corr.JPG

Please see the file attached.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

You probably need to use advanced aggregation here to include your Fractile() calculation into another Fractile():

=if(GetSelectedCount([HideAbove90thPercentile])=0,

Fractile(Salary, .9),

if(GetSelectedCount([HideAbove90thPercentile])=1,

Fractile(Aggr( If( Salary < Fractile(TOTAL<Position> Salary,.9),Salary),Position, ID) ,.9)

))

View solution in original post

2 Replies
swuehl
MVP
MVP

You probably need to use advanced aggregation here to include your Fractile() calculation into another Fractile():

=if(GetSelectedCount([HideAbove90thPercentile])=0,

Fractile(Salary, .9),

if(GetSelectedCount([HideAbove90thPercentile])=1,

Fractile(Aggr( If( Salary < Fractile(TOTAL<Position> Salary,.9),Salary),Position, ID) ,.9)

))

summerrain
Contributor III
Contributor III
Author

Yes, that works perfectly. Thanks for the help!