This example shows how you can use the Year over Year Growth Aggregate using Member template to compare aggregate values for the same payout periods for the current and previous years.
Required Editions
| Available in: Enterprise, Unlimited, and Developer Editions that have Rebate Management enabled. |
Transaction Journal Records
Acme is a member of a rebate program. Here are the sample Transaction Journal records for Acme for January 2021 and January 2020.
| Member | Activity Date | Transaction Quantity | Transaction Amount |
|---|---|---|---|
|
Acme |
05 Jan 2021 | 150 | $50,000 |
| 14 Jan 2020 | 60 | $15,000 |
Benefit Tiers
The rebate program offers a growth-based rebate type. The program has 12 monthly payout periods. Members are rewarded if they record an increase in sale quantity compared to the same payout period for the previous year.
The program manager defines these benefit tiers for the growth-based rebate type:
- Tier 1: Offers a 5% rebate on the total transaction amount when the growth quantity is less than 50 units.
- Tier 2: Offers an 8% rebate on the total transaction amount when the growth quantity exceeds 51 units.
Data Processing Engine
The Transaction Journal records for a payout period are filtered and aggregated for the current year and the previous year. The definition then adds the member’s previous transaction amount, current transaction amount, previous transaction quantity, and current transaction quantity. Total values are updated in the Rebate Member Product Aggregate object. One aggregate record is created per rebate type per member.
If you've customized the Year over Year Growth Aggregate using Member Data Processing
Engine definition or based it on an earlier template, update your aggregation logic to
correctly handle leap year scenarios. Replace the function ADDMONTHS({EndDate},
-12) with the following expression: IF((SUBSTR(TEXT({EndDate}),6,2) !=
SUBSTR(TEXT(ADDDAYS({EndDate},1)),6,2)),ADDDAYS(DATEVALUE(CONCAT(SUBSTR(TEXT(ADDMONTHS(ADDMONTHS({EndDate},-12),1)),0,8),
"01"), "yyyy-MM-dd"),-1),ADDMONTHS({EndDate} ,-12)).
Replace ‘{EndDate}’ with the actual date field used in your DPE definition. This update ensures accurate year-over-year calculations regardless of leap years.
Aggregate Data
This sample record is created in the Rebate Member Product Aggregate object for January.
| Record | Member | Prior Total Quantity | Total Quantity | Growth Quantity | Growth Quantity Percent | Prior Total Amount | Total Transaction Amount | Growth Amount | Growth Amount Percent |
|---|---|---|---|---|---|---|---|---|---|
| Record 1 | Acme | 60 | 150 | 90 | 60% | $15,000 | $50,000 | $35,000 | 70% |
These values update whenever the scheduled flow runs. The flow triggers the data processing engine definition. The aggregate values on the payout calculation date for Jan FY21 are used to update the final payout amount in the Member Payout object.
Final Payout
Acme’s payout is based on the Tier 2 benefit because the growth quantity exceeds 50 units. So, for January 2021, Acme receives $4,000 (8% of $50,000).