In this 15 minutes recording we explore the aggregate function of Variable Pay with the following business case : how to build data checks allowing HR and administrators to focus on employees with complex data situations before launching the annual bonus cycle.
The aggregate function which is also demoed in this blog (#3) is key in Variable Pay and allows us to build very complex configurations.
In this example we “roll up” (technical word for aggregate) from Assignments to Entry level, but it can also be used to roll up from Business Goal level to Business Detail level to Assignment level to Entry level. Or from Individual Goal level to Individual Detail level to Assignment to Entry level as was done in this specific configuration. Another business case using this very design is documented here (see presentation and specific template created to allow Comp Admin to load different earnings into a custom object).
Please note it is technically impossible to show every assignment value of an employee also at the entry level (for an employee with Target Bonus % of 5, 10 and 15 for example we could show the values of the first and last assignments – 5 and 15 in this case – but not the values in between). The only way to get all assignment level values in a consolidated way (and outside of the Admin UI) for HR and planners is to use the assignment details CSV export from Executive Review or have Comp Administrators create a specific report for that purpose).
Please find below the XML of the key fields used in the demo.
- Assignment level:
<comp-field-definition id="ALCF_Records_Count" isCustomField="true" isVisible="true" type="amount" useFor="varpay" level="assignment" readOnly="true" width="6" hidePercentage="false" hideAmount="false" percentageReadOnly="false" reloadable="false" auditable="false" displayOrder="1" reportable="true" needCalcTotal="true">
<comp-field-label><![CDATA[ALCF Records Count]]></comp-field-label>
<comp-field-number-format-id><![CDATA[defMoneyFormat]]></comp-field-number-format-id>
<comp-custom-field-formula><![CDATA[1]]></comp-custom-field-formula>
</comp-field-definition>
<comp-field-definition id="ALCF_target_Bonus_pct_imported_from_DM" isCustomField="true" isVisible="true" type="percent" useFor="varpay" level="assignment" importKey="tgtPct" importSrc="DM" readOnly="true" width="6" hidePercentage="false" hideAmount="false" percentageReadOnly="false" reloadable="false" auditable="false" displayOrder="2" reportable="true" needCalcTotal="false">
<comp-field-label><![CDATA[ALCF Target Bonus Pct (import from DM)]]></comp-field-label>
<comp-field-number-format-id><![CDATA[defMoneyFormat]]></comp-field-number-format-id>
</comp-field-definition>
<comp-field-definition id="ALCF_Tgt_Bonus_Pct" isCustomField="true" isVisible="true" type="percent" useFor="varpay" level="assignment" readOnly="true" width="6" hidePercentage="false" hideAmount="false" percentageReadOnly="false" reloadable="false" auditable="false" displayOrder="3" reportable="true" needCalcTotal="false">
<comp-field-label><![CDATA[ALCF Target Bonus Pct ]]></comp-field-label>
<comp-field-number-format-id><![CDATA[defMoneyFormat]]></comp-field-number-format-id>
<comp-custom-field-formula><![CDATA[(ALCF_target_Bonus_pct_imported_from_DM*1)/1]]></comp-custom-field-formula>
</comp-field-definition>
<comp-field-definition id="ALCF_salary_imported_from_DM" isCustomField="true" isVisible="true" type="money" useFor="varpay" level="assignment" importKey="salary" importSrc="DM" readOnly="true" width="6" hidePercentage="false" hideAmount="false" percentageReadOnly="false" reloadable="false" auditable="false" displayOrder="4" reportable="true" needCalcTotal="true">
<comp-field-label><![CDATA[ALCF Salary (import from DM)]]></comp-field-label>
<comp-field-number-format-id><![CDATA[defMoneyFormat]]></comp-field-number-format-id>
</comp-field-definition>
<comp-field-definition id="ALCF_Salary" isCustomField="true" isVisible="true" type="amount" useFor="varpay" level="assignment" readOnly="true" width="6" hidePercentage="false" hideAmount="false" percentageReadOnly="false" reloadable="false" auditable="false" displayOrder="5" reportable="true" needCalcTotal="true">
<comp-field-label><![CDATA[ALCF Salary]]></comp-field-label>
<comp-field-number-format-id><![CDATA[defMoneyFormat]]></comp-field-number-format-id>
<comp-custom-field-formula><![CDATA[(ALCF_salary_imported_from_DM*1)/1]]></comp-custom-field-formula>
</comp-field-definition>
<comp-field-definition id="ALCF_Bonus_Plan_imported_from_DM" isCustomField="true" isVisible="true" type="string" useFor="varpay" level="assignment" importKey="incentivePlan" importSrc="DM" readOnly="true" width="6" hidePercentage="false" hideAmount="false" percentageReadOnly="false" reloadable="false" auditable="false" displayOrder="6" reportable="true">
<comp-field-label><![CDATA[ALCF Bonus Plan (import from DM)]]></comp-field-label>
</comp-field-definition>
<comp-field-definition id="ALCF_Bonus_Plan_Number" isCustomField="true" isVisible="true" type="amount" useFor="varpay" level="assignment" readOnly="true" width="6" hidePercentage="false" hideAmount="false" percentageReadOnly="false" reloadable="false" auditable="false" displayOrder="7" reportable="true" needCalcTotal="true">
<comp-field-label><![CDATA[ALCF Bonus Plan Number]]></comp-field-label>
<comp-field-number-format-id><![CDATA[defMoneyFormat]]></comp-field-number-format-id>
<comp-custom-field-formula><![CDATA[if(ALCF_Bonus_Plan_imported_from_DM="VRP_Sample_Indiv",1.10,
if(ALCF_Bonus_Plan_imported_from_DM="VRP_Sample_Mgr",2.01,
if(ALCF_Bonus_Plan_imported_from_DM="VRP_Sample_SnrMgr",3.10,
if(ALCF_Bonus_Plan_imported_from_DM="VRP_Sample_Exec",4.01,
0))))]]></comp-custom-field-formula>
</comp-field-definition>
- Entry level:
<comp-field-group id="group6" useFor="varpay" displayOrder="11" backgroundColor="CAD378" displayColumn="NumberOfAssignments;;">
<comp-field-label><![CDATA[Data Checks (HR only)]]></comp-field-label>
<comp-field-definition id="NumberOfAssignments" isCustomField="true" isVisible="true" type="amount" useFor="varpay" aggregateField="ALCF_Records_Count" readOnly="true" width="6" hidePercentage="false" hideAmount="false" percentageReadOnly="false" reloadable="false" auditable="false" displayOrder="11" reportable="true" needCalcTotal="true">
<comp-field-label><![CDATA[# of Assignments]]></comp-field-label>
<comp-field-number-format-id><![CDATA[defMoneyFormat]]></comp-field-number-format-id>
</comp-field-definition>
<comp-field-definition id="AggregateTargetPct" isCustomField="true" isVisible="true" type="amount" useFor="varpay" aggregateField="ALCF_Tgt_Bonus_Pct" readOnly="true" width="6" hidePercentage="false" hideAmount="false" percentageReadOnly="false" reloadable="false" auditable="false" displayOrder="11" reportable="true" needCalcTotal="true">
<comp-field-label><![CDATA[Aggregate of Target % (hidden)]]></comp-field-label>
<comp-field-number-format-id><![CDATA[defMoneyFormat]]></comp-field-number-format-id>
</comp-field-definition>
<comp-field-definition id="LastTargetBonusPct" isCustomField="true" isVisible="true" type="percent" useFor="varpay" readOnly="true" width="6" hidePercentage="false" hideAmount="false" percentageReadOnly="false" reloadable="false" auditable="false" displayOrder="11" reportable="true" needCalcTotal="false">
<comp-field-label><![CDATA[Last Target Bonus % (hidden)]]></comp-field-label>
<comp-field-number-format-id><![CDATA[defMoneyFormat]]></comp-field-number-format-id>
<comp-custom-field-formula><![CDATA[ALCF_Tgt_Bonus_Pct]]></comp-custom-field-formula>
</comp-field-definition>
<comp-field-definition id="TargetBonusPctChange" isCustomField="true" isVisible="true" type="string" useFor="varpay" readOnly="true" width="6" hidePercentage="false" hideAmount="false" percentageReadOnly="false" reloadable="false" auditable="false" displayOrder="11" reportable="true">
<comp-field-label><![CDATA[Target Bonus % change in FY ?]]></comp-field-label>
<comp-custom-field-formula><![CDATA[if(LastTargetBonusPct*NumberOfAssignments=AggregateTargetPct,"No","Yes")]]></comp-custom-field-formula>
</comp-field-definition>
<comp-field-definition id="AggregateSalary" isCustomField="true" isVisible="true" type="amount" useFor="varpay" readOnly="true" width="6" hidePercentage="false" hideAmount="false" percentageReadOnly="false" reloadable="false" auditable="false" displayOrder="11" reportable="true" needCalcTotal="true">
<comp-field-label><![CDATA[Aggregate of Salary (hidden)]]></comp-field-label>
<comp-field-number-format-id><![CDATA[defMoneyFormat]]></comp-field-number-format-id>
</comp-field-definition>
<comp-field-definition id="LastSalary" isCustomField="true" isVisible="true" type="amount" useFor="varpay" readOnly="true" width="6" hidePercentage="false" hideAmount="false" percentageReadOnly="false" reloadable="false" auditable="false" displayOrder="11" reportable="true" needCalcTotal="true">
<comp-field-label><![CDATA[Last Salary (hidden) ]]></comp-field-label>
<comp-field-number-format-id><![CDATA[defMoneyFormat]]></comp-field-number-format-id>
<comp-custom-field-formula><![CDATA[ALCF_Salary]]></comp-custom-field-formula>
</comp-field-definition>
<comp-field-definition id="SalaryChange" isCustomField="true" isVisible="true" type="string" useFor="varpay" readOnly="true" width="6" hidePercentage="false" hideAmount="false" percentageReadOnly="false" reloadable="false" auditable="false" displayOrder="11" reportable="true">
<comp-field-label><![CDATA[Salary change in FY ?]]></comp-field-label>
<comp-custom-field-formula><![CDATA[if(LastSalary*NumberOfAssignments=AggregateSalary,"No","Yes")]]></comp-custom-field-formula>
</comp-field-definition>
<comp-field-definition id="AggregateBonusPlanNumbers" isCustomField="true" isVisible="true" type="amount" useFor="varpay" readOnly="true" width="6" hidePercentage="false" hideAmount="false" percentageReadOnly="false" reloadable="false" auditable="false" displayOrder="11" reportable="true" needCalcTotal="true">
<comp-field-label><![CDATA[Aggregate of Bonus Plan Numbers (hidden)]]></comp-field-label>
<comp-field-number-format-id><![CDATA[defMoneyFormat]]></comp-field-number-format-id>
</comp-field-definition>
<comp-field-definition id="LastBonusPlanNumber" isCustomField="true" isVisible="true" type="amount" useFor="varpay" readOnly="true" width="6" hidePercentage="false" hideAmount="false" percentageReadOnly="false" reloadable="false" auditable="false" displayOrder="11" reportable="true" needCalcTotal="true">
<comp-field-label><![CDATA[Last Bonus Plan Number (hidden)]]></comp-field-label>
<comp-field-number-format-id><![CDATA[defMoneyFormat]]></comp-field-number-format-id>
<comp-custom-field-formula><![CDATA[ALCF_Bonus_Plan_Number]]></comp-custom-field-formula>
</comp-field-definition>
<comp-field-definition id="BonusPlanChange" isCustomField="true" isVisible="true" type="string" useFor="varpay" readOnly="true" width="6" hidePercentage="false" hideAmount="false" percentageReadOnly="false" reloadable="false" auditable="false" displayOrder="11" reportable="true">
<comp-field-label><![CDATA[Change of Bonus Plan during FY ?]]></comp-field-label>
<comp-custom-field-formula><![CDATA[if(LastBonusPlanNumber*NumberOfAssignments=AggregateBonusPlanNumbers,"No","Yes")]]></comp-custom-field-formula>
</comp-field-definition>
</comp-field-group>
To build data checks as per my example but that will return the exact value the person had in a text column throughout the Fiscal Year (for example Bonus Plan or Currency) we can use the number suites logic of this file.
All the best,
Xavier