1. Implementing Incentive Compensation
  2. Value Set Aggregate Functions in the Expression Builder

Value Set Aggregate Functions in the Expression Builder

Use value set aggregate functions when you want to aggregate a list of values provided by the select SQL query of the user-defined query into a single value.

The expression causes an error in the calculation process if the user-defined query provides a list of values without using a value set aggregate function. If a user-defined query returns a list of values and you don't use a value set aggregation function around it, then the expression will remain valid but the earnings calculation will fail. The description of a user-defined query includes whether it needs an aggregate function.This table provides details about value set aggregate functions.

Detail

Description

Syntax

SUM_VALUE_SET ( User Defined Query()* )

COUNT_VALUE_SET ( User Defined Query()* )

MAX_VALUE_SET ( User Defined Query()* )

MIN_VALUE_SET ( User Defined Query()* )

AVG_VALUE_SET ( User Defined Query()* )

COUNT_DISTINCT_VALUE_SET ( User Defined Query()* )

Inputs

User-defined query (required) which returns a list of values

Description

It will work similar to the SQL Aggregate Functions SUM, COUNT, MAX, MIN, AVG, and 'COUNT DISTINCT' and will perform the aggregation on the list of values provided by the user-defined query. During calculation, this will add the aggregate function to the Value Column Name text box (within the SELECT clause of the SQL query) of the associated table validated value set.

Example

Target query to add to a user-defined query: SELECT SUM(output_achieved_ptd) FROM cn_srp_per_form_metrics_all

Perform the following setup steps:

  1. Create this query in the table validated value set of the user defined query called UDQ 1: SELECT output_achieved_ptd FROM cn_srp_per_form_metrics_all

  2. Add the following expression using the expression builder (Manage Expressions in Manage Compensation Plans) : SUM_VALUE_SET ( UDQ 1 () )

UDQ 1 () could return: 1000, 2000, 3000 while SUM_VALUE_SET ( UDQ 1 () ) would return 6000.

Expression Usage

Unlike existing aggregate functions, value set aggregate functions don't change the usage of the expression to a per interval or a group-by scenario. Add an existing aggregation function to the user-defined query to do so.

Exceptions

In cases where the user-defined query doesn't return any records, its output will be null. In such cases COUNT_VALUE_SET and COUNT_DISTINCT_VALUE_SET will return 0 while SUM_VALUE_SET, MAX_VALUE_SET, MIN_VALUE_SET, and AVG_VALUE_SET will return null. To avoid these scenarios, it's advisable to use the NVL function around the user-defined query.

This table shows examples of expressions and their usage.

Expression

Usage

Credit.Credit Amount * COUNT_VALUE_SET ( UserDefinedQuery () )

This is a valid expression which can be used in a per event or individual scenario.

SUM (Credit.Credit Amount) - SUM (UserDefinedQuery ())

This is a valid expression. The SUM around the User Defined Query was added to make the expression valid for a per interval or group-by scenario. In this case, the user-defined query returns only one value, otherwise the earnings calculation will produce an error.

SUM (Credit.Credit Amount) + MAX ( SUM_VALUE_SET ( UserDefinedQuery () ) )

This is a valid expression. The SUM_VALUE_SET is added to total the list of values provided by the user-defined query. The MAX around SUM_VALUE_SET is added to make the expression valid for a per interval or group-by scenario