Functions of Custom Compensation

The functions enable you to create dynamic, data-driven formulas based on employee attributes and compensation items, allowing you to develop tailored compensation strategies for your employees. When building compensation logic in Custom Compensation, these functions can be accessed easily by typing the @ symbol. This brings up a list of the following functions that you can use to define your compensation calculations.

RangeLookup

This function allows you to perform lookups based on salary bands or tax brackets, making it easier to handle compensation calculations that vary depending on an employee's salary or other attributes.

Syntax

RangeLookup([range_attribute], Source)

Arguments

• range_attribute: The name of the Range Attribute (e.g., Salary Band).

• Source: A compensation item or attribute to be matched (e.g., Salary).

Use Case

The HR team wants to apply a Salary Band Rate (percentage) to employees based on their salary range. The percentage varies depending on the salary bracket, and this percentage is applied in compensation calculations.

1. Define the Salary Range Attribute using the Range Attribute. Click here to learn more about the Range attribute.
    

2. Create a custom compensation, Salary Band Rate. This compensation item determines the applicable Salary Band Rate based on an employee’s salary.
   Salary Band Rate Calculation:
   The RangeLookup function determines an employee’s position within the predefined Salary Range by referencing Salaries. This ensures that compensation aligns with structured pay scales.

3. Map this custom compensation to the employee. When applied, the function determines the Salary Band Rate for each employee based on their salary:
   For example, the Salary Band Rate calculation for Sherry Young is shown below.
    

RangeCalculation.Floor

This function applies a flat calculation based on the value of a specified range. The compensation rate is determined solely by the band in which the value falls, without considering other bands.

Syntax

RangeCalculation.Floor([range_attribute], Source)

Arguments

• range_attribute: The name of the Range Attribute.

• Source: A compensation item or attribute to be matched.

Notes

• Applies only one contribution rate based on the band the value falls into.

Limitations

• Doesn’t accumulate across bands; flat rate only.

Example

Sales Performance Evaluation is calculated by comparing an employee’s actual sales achievements against predefined performance bands. A flat-rate approach is used, meaning the employee receives a single contribution rate based on the band their total sales fall into, regardless of how much falls into other bands.

In a Floor-based calculation, the contribution rate only applies to the specific salary band in which the salary falls.

Employee Salary = £30,000

• The salary falls within the £25,001 - £50,000 band.

• The contribution rate for this band is 10%.

• Calculation: £30,000 * 10% = £3,000

Key observation: Only the rate for the specific salary band is applied, without any tiered accumulation.

RangeCalculation.Tier

This function is designed for tiered compensation structures, where different compensation rates apply progressively across multiple bands. Instead of a single flat rate, earnings accumulate based on the employee's performance or sales milestones.

Syntax

RangeCalculation.Tier([range_attribute], Source)

Arguments

• range_attribute: The name of the Range Attribute.

• Source: A compensation item or attribute to be matched.

Notes

• Applies progressive rates across multiple thresholds (like tax slabs).

• Requires detailed range configuration.

Example

Sales Performance Evaluation is calculated by comparing an employee’s actual sales against predefined performance thresholds. A tiered structure is used, where compensation is accumulated progressively across multiple levels, rewarding performance at each stage.

Employee Salary = £30,000

• £0 - £10,000: 0% → £10,000 * 0% = £0

• £10,001 - £25,000: 5% → £15,000 * 5% = £750

• £25,001 - £30,000: 10% → £5,000 * 10% = £500

Total Contribution = £1,250

Key observation: Each salary band’s rate is applied cumulatively.

Use Case

UK National Insurance (NI) requires employees to contribute based on earnings exceeding specific thresholds. Contributions follow a tiered structure, where different portions of income are taxed at different rates. This calculation is performed using range attributes and the tier-based function in custom compensation.

1. Define the UK NI Attribute using the Range Attribute. Click here to learn more.
   

2. Create a custom compensation item UK National Insurance (Tier) using the RangeCalculation.Tier function, referencing the salary compensation item.

3. Map this custom compensation to the employee. When applied, the function calculates NI contribution based on salary.

   Note: The data is available as a Dimension Member in Dynamic Reports & Dashboards, allowing organizations to track NI costs efficiently.

   
   

Date-Based Functions

The functions enable you to create dynamic, data-driven formulas based on employee attributes and compensation items, allowing you to develop tailored compensation strategies for your employees. When building compensation logic in Custom Compensation, these functions can be accessed easily by typing the @ symbol. This brings up a list of the following functions that you can use to define your compensation calculations.

SOMONTH

Returns the first day of the month after applying an offset. Used for planning period start dates or eligibility logic.

Syntax

SOMONTH([start_date], months)

Arguments

• start_date: The base date (e.g., Hire Date).

• months: Offset in months (positive or negative).

  • Positive values return the first day of a future month.

  • Negative values return the first day of a past month.

EOMONTH

Determines the last day of the month for date-based calculations, particularly useful for anniversary bonuses or year-end calculations.

Syntax

EOMONTH([start_date], months)

Arguments

• start_date: The base date (e.g., Hire Date).

• months: Offset in months (positive or negative)

  • Positive values return the last day of a future month.

  • Negative values return the last day of a past month.

FISCAL_YEAR

Helps determine the fiscal year based on a date, which is important for planning and year-based compensation adjustments.

Syntax

FISCAL_YEAR(input_date)

Arguments

• input_date: Any date input (e.g., Hire Date, Termination Date).

PERIOD_DATE

Used for precise date handling, particularly for processing compensation based on periods or fiscal dates.

Syntax

PERIOD_DATE(output_type, offset)

• output_type: Typically a date attribute (e.g., Review Date).

• offset: Integer value indicating period offset.

Other Functions

UnadjustedSalary

This function calculates the salary impact based on an employee’s termination date. This function determines whether an employee’s salary should be reduced or removed from the payroll when their termination falls within the reporting period.

Syntax

UnadjustedSalary()

Arguments

None.

Notes

• The UnadjustedSalary() function currently considers only the wage type, excluding other compensation items.

• Used to calculate the base salary impact when an employee is terminated.

• Ignores other items like bonuses or benefits.

Limitations

• Only applicable for wage-type compensation.

IF

The IF function enables conditional logic in your custom compensation formulas by returning different values based on whether a condition is true or false. This is useful for handling performance-based bonuses, eligibility rules, or any logic that varies by employee attributes or compensation thresholds.

The function evaluates a condition, and:

• If the condition is true, it returns the value_if_true.

• If the condition is false, it returns the value_if_false.

This allows you to easily create branching logic based on employee data without needing complex nested expressions.

Syntax

IF(condition, value_if_true, value_if_false)

Arguments

• condition: Boolean expression.

• value_if_true: Value returned when the condition is true.

• value_if_false: Value returned when the condition is false.

ISBLANK

This function checks whether a specified employee date attribute is blank or unassigned. Helps ensure that formulas work correctly even when data is missing.

ISBLANK(input_date)

• input_date: Must be a date-type attribute.

Notes

• The ISBLANK function is designed to work only with date-based attributes. It is not compatible with text or numeric fields. For instance, ISBLANK(Bonus) will not return valid results if Bonus is a number.

NOT

This function reverses the result of a Boolean expression. Commonly used to simplify conditional logic, particularly for “not equal” or “not blank” checks.

Syntax

NOT([logical_condition])

Arguments

• Any logical statement (e.g., comparisons, ISBLANK).

Examples

Use Cases

• Exclude certain employee conditions from logic (e.g., “if NOT promoted”)

• Simplify expressions that would otherwise need complex logic chaining

ProrationFactor

ProrationFactor makes it easier to account for real-world scenarios—like mid-period hires, terminations, or department transfers—by automatically adjusting compensation amounts based on how much of the period an employee was active. This means you can create time-sensitive compensation models without manually calculating partial-period adjustments.

By dynamically adjusting amounts based on the number of days worked, this function improves the accuracy of Custom Compensation and goes beyond basic date checks to offer more granular control.

ProrationFactor("Input")

• ActiveDays: Returns the portion of the period the employee was actively working. Active days include any day the employee is employed.

• InactiveDays: Returns the portion of the period the employee was not actively working. Inactive days include time before the start, after termination, or during unpaid leave.

Notes

• Returns a decimal between 0 and 1.

• Depends on the Workday setup.

Use Case

The HR team wants to calculate employees' compensation accurately based on their actual days worked during a month. For example, if an employee joins on June 11 in a 30-day month, only the remaining 20 days should be considered for salary calculation. The employee’s full monthly salary is $3,000, but because they were only active for 20 out of 30 days, the prorated amount paid is $2,000.

Additional Use Cases with Calculations

Below are the other use cases:

Function Behavior in Different Situations

• If the employee has 0 active days, the function returns 0 when using ActiveDays.

• If the employee has 0 inactive days, the function returns 0 when using InactiveDays.

• The function is based on the workdays setup. Click here to learn more about Workday Setup.

• If the function is used before the employee’s position start date, no value is returned.

EDATE()

The EDATE() function returns a date that is a specified number of time units (months, days, or years)  before or after a given start date, making it useful for dynamic date calculations.

Key Features

• Supports day, month, and year offsets.

• Allows negative values to calculate dates in the past.

• Defaults to the month unit if the unit is not specified.

• Provides clear error messages for incorrect usage.

• Supports both forward and backward date shifts.

• Clamps day to the end of the month when overflow occurs.

Syntax

The standard syntax, where the offset_value is in months.

EDATE( [start_date], offset_value)

Alternate Syntax

In situations where you need date shifts to be dynamic based on different time scales, you can use a unit parameter that allows specifying the unit of time.

EDATE(start_date, offset, [unit])

Arguments

• start_date: The original date (in this case, the employee’s hire date).

• offset_value: The number of months to shift the date forward or backward. A value of 12 moves the date forward by one year

• unit (optional):

  • "m" for months (default)

  • "y" for years

  • "d" for days
    If the unit is omitted, it defaults to months, mimicking Excel-like behavior

Examples:

• EDATE("2025-01-01", 2) → 2025-03-01

• EDATE("2025-01-01", 10, "d") → 2025-01-11

• EDATE("2025-01-01", -5, "m") → 2024-08-01

• EDATE("2025-01-01", 3, "y") → 2028-01-01

Notes

• Syntax Validation:

  • Must include at least two arguments.

  • If the optional third argument is used, it must be one of "d", "m", or "y".

• Argument Validation:

  • start_date must be a valid date.

  • offset must be an integer.

  • Only one unit type is allowed. Mixed or invalid units like "md" will trigger a validation error.

Example

Awarding an Anniversary Bonus Based on Hire Date

The following formula determines whether an employee is eligible for an anniversary bonus by checking if their hire date aligns with the current reporting period:

IF(EDATE(Attr[Hire Date], 12) = PERIOD_DATE("start", 0), Comp[Anniversary Bonus], 0)

This formula adds 12 months to the employee’s hire date using the EDATE function to calculate the first work anniversary. It then compares this adjusted date to the current period’s start date using PERIOD_DATE("start", 0).

• If the anniversary date falls on the start of the current reporting period, the employee qualifies for the Anniversary Bonus specified in the compensation data.

• If not, the bonus value defaults to 0.

DATEDIF

The DATEDIF() function computes the difference between two dates in terms of years, months, or days. This supports more precise tenure-based and eligibility calculations.

Syntax

DATEDIF( [start_date],[end_date], "unit")

Arguments

• start_date: A valid date attribute or a date-producing function.

• end_date: A valid date attribute or date-producing function.

• unit:

  • "d" for days

  • "m" for months

  • "y" for years

Note: Both start_date and end_date must be valid date expressions. The unit is mandatory and must be one of the accepted types.

Key Behavior Rules

1. Inclusive Date Range for "d" (days): Includes both start and end dates in the count.Example: DATEDIF("2025-05-12", "2025-05-15", "d") → 4

2. Negative Differences Supported: If start_date > end_date, the result will be negative. Example: DATEDIF("2025-05-15", "2025-05-12", "d") → -4

3. Exact Month and Year Units: "m" (months) and "y" (years) count only full units.

   • Partial months or years are not counted.

   • Example: DATEDIF("2025-01-31", "2025-02-28", "m") → 0

4. Accurate Day Counts Across Calendar Months: Day calculations honor varying month lengths. Example: DATEDIF("2025-01-31", "2025-02-02", "d") → 3

Example

Evaluating Eligibility for a Short-Term Bonus Based on Tenure

The following formula determines whether an employee qualifies for a short-term bonus based on the length of time they held a specific position:

IF(DATEDIF(Attr[Position Start Date], Attr[Position End Date], "m") < 6, Comp[Short-Term Bonus], 0)

This logic calculates the number of complete months between the employee's position start date and end date. If the tenure is less than 6 months, the employee is eligible to receive the Short-Term Bonus specified in the compensation data. If the tenure is 6 months or more, the bonus amount defaults to 0, indicating no short-term bonus is awarded.

DATE

The DATE() function defines dates explicitly in formulas, which helps in performing direct and date comparisons.

Key Features

• Supports date format (YYYY-MM-DD).

• Supports syntax to include an optional parameter to define other date formats

• Enables date comparison without type mismatch.

• Provides error messages for invalid or ambiguous formats.

Syntax

DATE( "MM/DD/YYYY")

Arguments

"YYYY-MM-DD": A string representing a specific date (year-month-day). This date is compared directly to the employee's hire date.

Alternate Syntax

In situations where you need to input dates in non-default formats (e.g., U.S. or European formats), the DATE() function supports an optional second parameter to explicitly define the intended format. This helps avoid ambiguity and ensures the expression engine correctly interprets the input.

You can use the alternative syntax:

• If the month and day order is locale-specific and ambiguous

• If you copy-paste from Excel or external systems that use formats like "MM/DD/YYYY"

If the date is passed in a non-YYYY-MM-DD format and no second parameter is provided, it gives a validation message.

Supported Alternative Syntax Patterns

Notes

Years must be 4-digit (YYYY) – two-digit (YY) formats are not supported

Example

Determining Merit Cycle Eligibility Based on a Hire Date

In organizations where the performance cycle runs from June to June, employees are often required to be employed for a certain minimum period before they become eligible for merit increases. A common policy is to include only those employees who were hired before the cycle start date — for example, June 1st.

To determine if an employee is eligible for the current Merit Cycle (2024–2025), we check if their hire date falls before June 1, 2024.

IF(Attr[Hire Date] = DATE("2024-06-01"), Comp[Bonus], 0)

This formula checks whether the employee’s hire date matches June 1, 2024. If the condition is true, the employee is included in the merit cycle, and the system uses the value from the Comp[Bonus] field. If the hire date does not match the specified date, the result is 0, meaning not eligible.

Agg() Function

With this release, a new function Agg(Input, TrailingMonths, OffsetMonths) has been added to the Custom Compensation feature to support dynamic rolling aggregations across monthly windows.

This function enables users to calculate trailing or forecasted values such as commissions, bonuses, or quota achievements using flexible, scalable syntax. It operates at the employee level and supports aggregation across fiscal years and offset periods.

• Accepts Compensation Items (e.g., Comp[Commission]) and Integer-based By Period Attributes (e.g., Attr[Target]).

• Supports trailing month windows and offsetting (forward or backward) from the current period.

• Handles missing months by defaulting values to 0—no errors are thrown.

• Enables cross-fiscal year aggregation logic.

Function Syntax

Agg(Input, TrailingMonths, OffsetMonths)

Examples

Use Cases

• Trailing 6-month commissions

• Prior-year quota achievements

• Future-dated forecast aggregations