How to Use Percentiles Configuring Data Source Formulas – PeopleFluent

Summary
How Compensation Administrators Can Use Percentiles Configuring Data Source Formulas

Applicable Products
Talent Management – Compensation

Applicable Releases: 11.19 and higher

Detailed Information:
The purpose of this article is to describe how a Compensation administrator can use percentiles when configuring data source formulas.

Compensation administrators can now use percentiles when calculating measures in data sources, and use these new calculation methods in formulas. When adding or editing a measure on the Design tab of a data source, two new calculation methods are available:

Percentile Discrete: Returns a value from the data set.
Percentile Continuous: Returns a computed result after doing linear interpolation

If one of these calculation methods is selected, a Percentile field appears in the Add/Edit Measure dialog box.

This field is used to specify the percentage value that is required. You can use these new measure calculation methods in reward cycle formulas.

Example: Quartile Salary Rankings

Here is a set of example reward cycle formulas to determine the ranking of an individual.

First, create a data source and add dimensions (curGrade and orgShortName) and measures that calculate .25, .5, and .75 percentiles.

This formula gets the 25th percentile value and stores it in a user-defined field (pct25):

def resultSet = Row.worksheet.getDataSourceAsList("Quartiles By Org Short Name and Current Grade");
def resultSetFiltered = resultSet.findAll{it.curGrade == currentGrade &&
it.orgShortName == Row.participant.org.shortName};
if ( resultSetFiltered.isEmpty() ) { return null; }
else {
return new BigDecimal(resultSetFiltered.get(0).get("pct25"));
}

Use a similar formula for the 50th and 75th percentiles, stored in separate user-defined fields (pct50 and pct75).

This next formula gets the actual quartile rankings by comparing an individual’s current pay to the percentile values calculated, based off of the top worksheet using employees with the same salary grade and in the same organization:

def resultSet = Row.worksheet.getTop().getDataSourceAsList("Quartiles By
Org Short Name and Current Grade");
def resultSetFiltered = resultSet.findAll{it.curGrade == currentGrade &&
it.orgShortName == Row.participant.org.shortName};
if ( resultSetFiltered.isEmpty() ) { return null; }
else {
pct25 = new BigDecimal(resultSetFiltered.get(0).get("pct25"));
pct50 = new BigDecimal(resultSetFiltered.get(0).get("pct50"));
pct75 = new BigDecimal(resultSetFiltered.get(0).get("pct75"));
}
if (localCurrentSalary < pct25) { return 1;}
if (localCurrentSalary < pct50) { return 2;}
if (localCurrentSalary < pct75) { return 3;}
if (localCurrentSalary >= pct75) { return 4;}
return null;

To support this enhancement, the following new pseudo-entity methods have been added:

getDataSourceAsCSV: Allows a data source to be invoked by data source name. Returns a comma-separated-value (CSV) output with data source dimension and measure alias as the column headers.
getDataSourceAsList: Allows a data source to be invoked by data source name. Returns output as a list of maps with data source alias name/value pairs.
getTop: Returns the top worksheet for the given reward cycle.

We're here to help