Calculating Percentile

[funkykizzy]

Hi June7,

I am trying to accomplish the same thing as https://www.accessforums.net/queries...26783.html#top with a large set of environmental data. I am using the same code that I got from http://www.utteraccess.com/forum/Cal...&mode=threaded.

Here is how I have it set up:
Public Function fnXPercentile(strFName As String, strTname As String, intX As Double) As Double


' dcount("*","tablename", "Field<=" & [Field] ranks
' the field between 1 and n including nulls
fnXPercentile = DMin(strFName, strTname, "Dcount(""*"", """ & strTname & """, """ & strFName & "<="" & [" & strFName & " ]) >= " & intX * DCount("*", strTname))
End Function

I call this as part of a Make Table query because I need to group my data by year to get the percentiles by year. As a test query, I am able to get the average by year (using the Totals ave query function), but only the total percentile of the entire data and not by grouping.

Can you see what I am missing here?

Thanks a million!!

[June7]

Why a Make Table query? Any query created to make the table could be used just as well as the table as the RecordSource for a report.

A report can group your data and do the percentile calcs.

Domain aggregates can really slow query performance and, as you are finding, be cumbersome because any filter criteria used in the query must be replicated in the domain function.

The function uses a DCount with a nested DCount. I expect both need the same filter criteria applied. My head is spinning.

A report handles the dirty work for you. It allows display of detail records and summary calcs.

Unless there is some compelling requirement to have it all accomplished within a query, I recommend a report.

If you want to provide db for analysis, follow instructions at bottom of my post.

[funkykizzy]

Hi June7!

Thanks for the quick reply!!

No reason for the Make Table query, although I tried to do the same with a report and got the same result.

I am currently trying to re-write my percentiles function.
I am using the Excel PERCENTILE(array, k) to check my results, so I need to make sure my calculations are performed the same way.
For Excel:
' Excel Percentiles Algorithm
' (N-1)*P = k + d, where N = record count, P = percentile with 0<P<1, and k = integer part and d = decimal part
' v_(k+1) + d*(v_k+2 - v_k+1) = our desired percentile. Where v_k is the value at the k position/rank

Using the data in my example database, I am trying to perform this calculation by the groupings. So, for the group of State, Method, Parameter, Year being a, a, a, 2001, the ranked values are 4, 34, and 56.
Applying the Excel equations to get the 90th percentile we have (N-1)*P = (3-2)*0.90 = 1.8. So k = 1 and d = 0.8.
Looking at our ranked values, v_(k+1) = v_2 = 34 and v_(k+2) = v_3 = 56.
Finishing the calculations we have 34 + 0.8(56 - 34) = 51.6.
The value in the "value" field of either a Make Table query or report is returning as 93 for all the groups, which is the 90th percentile of the ENTIRE database.

I think the problem is with my code, but I am having trouble getting the data by the groups so that I can apply my percentile calculation. I am trying to re-write using SQL code, but I get type mis-match errors.

Any thoughts?

[funkykizzy]

Oops, sorry. I just realized that I didn't post my example db.

Table1 is a dummy set of original data
Query1 is what I was trying to do (groupings and using function)
Percentiles table is what I was trying to achieve, but with different values for each group.

Thanks!

[June7]

I tried and gave up getting the additional parameters into the domain aggregate functions, especially the first nested DCount, just can't get the concatenation right.
Then I tried fnXPercentile function on a query filtered to the 3 aaa2001 records. It returns a value of 56. So the function is an incomplete calculation.

I tried the other function with following changes:

Public Function PercentileRst(RstName As String, fldName As String, PercentileValue As Double, strCriteria As String) As Double
...
Set RstOrig = CurrentDb.OpenRecordset("SELECT * FROM " & RstName & " WHERE State & Method & Parameter & Year='" & strCriteria & "'", dbOpenDynaset)
...
End Sub

Then call the function from query:

SELECT *, PercentileRst("Table1", "value", 0.9, [State] & [method] & [parameter] & [year]) AS 90th FROM Table1;

The result for the same 3 records is 51.6 and various returns for the other groups.


The second function performs much better. Domain aggregates can be very slow in queries.


BTW, year is a reserved word. Should not use reserved words as names.

[funkykizzy]

Thank you SO MUCH, June7! Your suggestions worked like a dream on my dummy data and, although a bit slow, did what I was looking for in my large, original database. I really appreciate your help! I can't believe you got that to work!

Thanks again!

[Dal Jeanis]

In case you ever need to repeat the process, I posted you a faster method on the other thread. https://www.accessforums.net/program...ode-40269.html