Hello,
I'm a little puzzled as to how to go about making a percentile analysis function for access. I essentially have a query with an item number, start_date, end_date fields. Then I have another expr field in the query that calculates datediff between start_date and end_date. This datediff expr tells me the days it took to complete each item. But there are outliers in the data. If there were a way to create a new field with an expression that shows me the percentile of each item number, I could use a criteria filter to remove items below 5% and above 95% (to remove top and bottom 5%).
What I've found so far is this module entry: But I don't know if its right or how to implement it.
Public Function XPercentile(FName As String, _
TName As String, _
X As Double) _
As Double
' FName = Field name
' TName = Table name
' x = decimal percentile (0.68 for 68%)
' Return the minimum value for which x% of
' the values are lower or equal to it
XPercentile = DMin(FName, TName, _
"DCount(""*"", """ & TName & """, """ & FName & _
"<="" & [" & FName & " ]) >= " & _
X * DCount("*", TName))
End Function
I'm a little puzzled as to how to go about making a percentile analysis function for access. I essentially have a query with an item number, start_date, end_date fields. Then I have another expr field in the query that calculates datediff between start_date and end_date. This datediff expr tells me the days it took to complete each item. But there are outliers in the data. If there were a way to create a new field with an expression that shows me the percentile of each item number, I could use a criteria filter to remove items below 5% and above 95% (to remove top and bottom 5%).
What I've found so far is this module entry: But I don't know if its right or how to implement it.
Public Function XPercentile(FName As String, _
TName As String, _
X As Double) _
As Double
' FName = Field name
' TName = Table name
' x = decimal percentile (0.68 for 68%)
' Return the minimum value for which x% of
' the values are lower or equal to it
XPercentile = DMin(FName, TName, _
"DCount(""*"", """ & TName & """, """ & FName & _
"<="" & [" & FName & " ]) >= " & _
X * DCount("*", TName))
End Function
