GETPERCn returns the value associated with the specified percentile of a specified expression. GETPERCn is an Advanced Math Toolbox Function.
SYNTAX:
GETPERCn(Exp, DateRange, optional TimeIncrement, optional Number of Values)
where :
n is the target percentile to be calculated
Exp is any valid WIMS Expression
DateRange is an expression in one of two forms:
1. StartDate TO EndDate
StartDate is a date literal. If StartDate is NoDate, then the function does not calculate and returns the value BLANK.
EndDate is a date literal. If EndDate is NoDate, then the function does not calculate and returns the value BLANK.
2. TimeRangeLiteral
A TimeRangeLiteral Defines start and end dates for a time interval specified. For a listing of available literals, refer to the TimeRange Literal List.
TimeIncrement is an optional parameter that specifies the time frame of the calculation. Default is BYDAY (Daily). For a listing of available literals, refer to the TimeIncrement Literal List.
Number of Values is an optional parameter that specifies the maximum number of values to process in the date range (default is 0). If the value is zero, then the function uses all the real values in the date range. If the number is 5, for example, then the function will only process the first five values in the date range. If the number of values requested are not found within the date range, then the function returns BLANK.
-
If you specify the number of values, you MUST also specify the Time Increment.
-
The order of the dates in Date Range is important. The function will search from the start date to the end date. Therefore, if you want the 90th percentile of the last 10 values specify D to D-30. This will search from the start date (D, i.e. the day being calculated) back to the end date 30 days prior. If you specify D-30 to D it would search from 30 days ago to the day being calculated and therefore find the first 10 values in the date range.
NOTES:
The GETPERC function returns the value from ordered list of values at the percentile "slot". The slot is determined by the following equation:
(Number of Samples) * (Percentile Desired) / 100
The value is then rounded (scientific, ie if exactly .5 round to the nearest even number) to the nearest whole number. This number determines which "slot" to use if all values are sorted into slots from lowest to highest value.
85 percentile of 10 numbers would pick the eight slot, ie 10 * 85/100 = 8.5 rounded = 8.
90 percentile of 10 numbers would pick the ninth slot, ie 10 * 90/100 = 9.
The Advanced Math Toolbox Functions give full flexibility to WIMS users to create new calculations that are not currently available with the existing, predefined functions. Since these functions are very general and very powerful, they do not have a lot of safety checks built into them and must be used with caution. The advanced functions will generally be slightly less efficient than the predefined functions and are not therefore intended to replace the predefined functions.
The main feature of the advanced functions is the ability to specify the date range for the calculations explicitly in the function call. This allows nonstandard time frames to be used for summation, averaging, geometric means, etc. The functions also allow calculations within the function call and thus are no longer limited to just working on a single variable. (i.e., you can now take the sum of C2 * C3)
The range of the advanced functions can be specified in two ways:
-
By explicit dates (i.e., sum all values from the beginning of the month to the end of the month.) See
Date Literals for more information on specifying dates.
-
By maximum number of values within a date range (i.e., sum the first 5 numbers from the start date to the end date) These range specifications apply to most of the custom functions.
EXAMPLE:
Example 1: Calculate the monthly 95th percentile of a variable.
GETPERC95(C88, #MM/1 TO TodayIf(D=#MM/LL))
95 : Calculate the 95th percentile
C88 : of variable 88
#MM/1 : from the first day of the current month being calculated
TO TodayIf(D=#MM/LL) : to today, if today is the last day of the current month being calculated
Example 2: Calculate the 90th percentile of the last 10 values (including the current date). Search up to 30 days.
GETPERC90(C88, D to D-30,BYDAY,10)
90 : Calculate the 90th percentile
C88 : of variable 88
D to D-30: Search from the day being calculated (D) back 30 days (D-30)
BYDAY: TimeRange Literal that specifies this is a daily variable.
10: Use the first 10 values found.
Example with MDL Rules:
V = GETPERC50(C5261, #MM/1 TO TodayIf(D=#MM/LL))
On calculated variables, it is possible to elect to cascade MDL rules or not. Below is an example of cascading MOST frequent MDL symbol, ALL MDL symbols, and NONE or no MDL symbols. This particular example is the same as using GETMED since we have requested the 50 percentile.
V9681 - Most Frequent MDL Symbol
V9682 - All MDL Symbols
V9683 - No MDL Symbols
Here is the data in variable 5261 that we are using for this example:
To verify the 50 percentile, which is also the median of our data set, we copy the values in Excel twice, making two rows of the same values. In one row we want to make them numeric that Excel can understand so we subtract 0.1 from values having '<' symbol and add 0.1 to values using '>' symbols. We then sort both rows together or use the Excel Median function to evaluate our data set. As we can see below, the median is 4.10 which equates to >4.0.
