How to calculate 90 percentile based on category
I need to calculate 90 percentile in one of my discovery (there is no option in model). Since there is no direct way to have 90 percentile, instead I am trying using standard deviation (1.2 above mean = 90 percentile).
But as in attached file, I got the result from all not based on category, please help.
11 replies
-
Please attach a sample dataset that you're trying to analyze as an Excel file perhaps.
Also, please be clear: do you want the 90 percentile of a metric looking across a set of results in a query, or the 90 percentile of the values, at the row level, sitting behind the results.
-
There is no way currently to select 90 percentile in the model.
You can try to do it via a semantic calculation based on a set of the relevant attribute:
https://help.pyramidanalytics.com/Content/Root/developer/reference/fx/PQL/Semantic/Statistical/Percentile.htmRegarding the comment of the 1.2 above mean (or 1.5 from median?), could you provide the calculation expression?
Should simply be stdev(measure) * 1.2 + avg(measure), or stdev(measure)*1.5 + median(measure).
-
Thanks Imbar.
stdev(measure)*1.2+ median(measure) should work, but the value does not the same when I use percentile (0.9) in Postgres, which is so confuse.
-
I think i understand what you did based on the column headers in the image.
The first 4 columns are indeed aggregations of the Length column, as you would calculate in Postgres.
The last column is the value 1.5 sdtev from the average of the Median Lenght column (second column), as a visual calculation - what value based on what is currently displayed in the grid.What you are looking for is to click on the second and fourth columns (median and stdev) and create:
Then open the calculation in formulate and add *1.2 next to the stdev:
This would be the same as writing 1.2*stdev(length) + median(length) in your Postgres query.
-
that is correct, the problem I met is the value calculated from percentile directly (from Postgres) and using calculation with stdev*1.2+median are the different after using category column.
-
-
I suggest that you should add an idea on the forum to have a 90th and 10th percentile as an aggregation in the model / formulate
As Imbar mentioned above you can do it via a semantic calculation based on a set of the relevant attribute. You will have to make the referral id visible by checking it in the model.
This calculation does require the entire list of granular referrals to be read before calculating the 90th percentile as opposed to a suggestion to perform the aggregation in the model