Subtracts (nets) successive values within an array on a measurement period basis by successively subtracting the previous value from each current value within each measurement period. #MPNET can be used in Census Specification Data Defaults and Service and Salary Transformation Expressions.

Syntax:

a #MPNET b

where

a is an integer or an array of non-decreasing dates (created by assignment) with a date for each value in b

b is an array, #DATE, #THIS or values created by assignment.

When a is:	the measurement period is:
1	plan year
2	calendar year
4	quarterly, ending on the plan year
5	quarterly, ending on the calendar year
12	monthly
24	semimonthly
0	a running subtraction from the first value to the last is created
-1 or blank	the transformation’s measurement period

When a is an array of non-decreasing dates (created by assignment) with a date for each value in b, then all b values with the same date are considered to be in the same measurement period. In a Data Default, when you create an array by assignment (e.g., Sals:= #VALUE SalaryHistory &), this is the only way to net the values on a measurement period basis (because #DATE is not available in a data default).

Note:

When used in Data Defaults, #DATE and #THIS are not available, and the left argument is not optional and cannot be -1. When used in Data Defaults as the last statement in the expression box, the right argument must be an array field, and #MPNET creates an array field (similar to #ARRAY)

When used in a transformation expression, #MPNET acts on array fields, #DATE, #THIS or values created by assignment. If the assignment is based on a start/stop array field (e.g., A:= Hours), then the values in the array are projected before being assigned to the temporary variable.

Examples:

Netting array fields in a Data Default

12 #MPNET Salary

This expression will return the monthly successively netted amounts of the measurement period values in array field Salary. For a start/stop array field, the stop date is used to determine in which measurement period each salary belongs; the effective date is used for an effective date array field. Since the measurement period is monthly, the first value in each month is unchanged. The second value in each the month is the second value in the month minus - the first value in the month; the 3rd value in each month is the 3rd value in the month minus the second value in the month; etc..

The example below illustrates how the given values in a Salary start/stop array field are successively netted within each month:

 

Start/stop arrays are first sorted by start date (ascending) and then by stop date (ascending). Next the stop date associated with each reported salary is used to determine to which month each salary belongs (see which mp? column). Finally, the values are successively netted within each month (as indicated by the specified measurement period, 12). 

Netting an array created by assignment in a Data Default

Suppose we need to adjust the reported salaries in a start/stop array field (e.g., Salary) using the start and stop dates and then net the adjusted salaries (AdjSals). For example, 

B_dt:= #START Salary &
E_dt:= #STOP Salary &
Sals:= #VALUE Salary &
AdjSals:= Sals * ((1+E_dt - B_dt) / 30 ) &

How can we successively net the values in AdjSals when we no longer know the date each salary is associated with? (I.e., since we're in a Data Default, neither #DATE nor calculation dates are available.)  The answer is to use an array as the left argument to #MPSUM where the array has the same number of elements as the right argument. The left argument indicates in which measurement period each salary belongs. For example, we could round each stop date to the end of a month and use that as the left argument; the key is to have the dates, for all salaries in the same measurement period, be the same.

EOM_dt:= #ENDMTH E_dt & ; round each stop date to the end of the month
EOM_dt #MPNET AdjSals ; successively on a monthly basis

Because each salary in the same measurement has the exact same date in the left argument, #MPNET can net the values appropriately:

Like the first sample expression above (12 #MPNET Salary), EOM_dt #MPNET AdjSals successively nets the values on a monthly basis: the first value in each month is unchanged; the second value of each month is the second value in the month minus the first value in the month; the 3rd value in each month is the 3rd value in the month minus the second value in the month; etc.. However, in this case the "measurement periods" are defined by the dates in EOM_dt which contains the stop date of each reported salary rounded to the end of the month. The thing to note here is that all values with the same date are successively netted. It does not matter whether those dates are end of month dates, beginning of the month dates, or the 17th of the month dates. The only thing that matters is that all the dates for a particular measurement period are the same and that the dates in EOM_DT are non-decreasing.

Note what happens when we use the actual stop date of each reported salary (instead of the end of month dates) to successively net the adjusted salaries in the same measurement period:

Only 2 salaries can be netted because only 2 salaries have the same stop date (i.e., 07/27/19)! 

Netting arrays in transformation expressions

Whether you start with an array field or create an array by assignment, in a transformation expression, they are treated quite differently. For example, if we assign Sals:= Salary &, then 12 #MPNET Salary and 12 #MPNET Sals can produce different results. For example, suppose your start/stop salary array contains these values:

​

Then, because #DATE is available in the transformation expression, the array field is used to populate an array with a calculation date set of values, where each reported salary shows up on its stop date. Note how each start date, stop date and the day before each start date is added to the set of calculation dates Because 2 salaries had the exact same stop date (7/27/19), in one instance the values were added together (within #MPNET) and in the other (Sals:= Salary &), the last value with the same stop date was used (3900.00). Note that in an Estimate calculation, the last reported value in a start/stop array will be projected (level) based on the Service or Salary Definition's measurement period (monthly in this example); in a Final calculation, there is no projection, so the values in red will be zero.

​

How to derive monthly salaries from reported salaries that are accumulated annually

​Suppose the client’s salary data is reported every month, but the amount for the month includes amounts from the beginning of the calendar year which is also the plan year. That is, if an employee earns $1000 every month, the amount reported for January is $1000; for February is $2000; for March $3000 and so on. The array has the following values:

Start Date	Stop Date	Amount
...	...	...
10/1/2010	10/31/2010	10,000
11/1/2010	11/30/2010	11,000
12/1/2010	12/31/2010	12,000
1/1/2011	1/31/2011	1,000
2/1/2011	2/28/2011	2,000
3/1/2011	3/31/2011	3,000
4/1/2011	4/30/2011	4,000
5/1/2011	5/31/2011	5,000
6/1/2011	6/30/2011	6,000
...	...	...

Two Data Dictionary arrays are setup. One, salary_1, is read from the database as the Start / Stop array above. The other, salary_2, also set up as Start / Stop array, is defaulted in the Census Specifications with the expression:

2 #MPNET salary_1

The result for salary_2 will be:

Start Date	Stop Date	Amount
. . .	. . .	. . .
10/1/2010	10/31/2010	1,000
11/1/2010	11/30/2010	1,000
12/1/2010	12/31/2010	1,000
1/1/2011	1/31/2011	1,000
2/1/2011	2/28/2011	1,000
3/1/2011	3/31/2011	1,000
4/1/2011	4/30/2011	1,000
5/1/2011	5/31/2011	1,000
6/1/2011	6/30/2011	1,000
. . .	. . .	. . .

B_dt:= #START salary_1 &
E_dt:= #STOP salary_1 &
S:= #VALUE salary_1 &
EOM_dt:= #ENDMTH E_dt &
EOM_dt #MPNET S

Like the first sample expression above (2 #MPNET salary_1), EOM_dt #MPNET S nets the values on a monthly basis.  However, in this case the "measurement periods" are defined by the dates in EOM_dt which contains the stop date of each reported salary, rounded to the end of the month. The thing to note here is that all values with the same date are netted. It doesn't matter whether those dates are end of month dates, beginning of the month dates, or the 17th of the month dates. The only thing that matters is that all the dates for a particular measurement period are the same and that the dates in EOM_DT are non-decreasing.

In ProAdmin, an array field is a collection of values (numerical, code, or date) that are correlated with or delimited by a date or a range of dates. Arrays fields are either:

• Effective-dated, where a single date indicates the date on and after which the value is in effect, or

• Start and stop-dated, where each value has two dates that span the time period over which the value is applicable.​

Examples:

Effective-dated array
Hourly Rate of Pay
Effective date	Value
5/15/2003	10.87
5/17/2004	11.52
5/18/2005	12.03

The rate of pay started on 5/15/2003 at $10.87 per hour. It then changed to $11.52 per hour on 5/17/2004, and then changed to $12.03 per hour on 5/18/2005.

Start and stop-dated array
Annual Compensation
Start date	Stop date	Value
1/1/2003	12/31/2003	21,945.25
1/1/2004	12/31/2004	23,455.52
1/1/2005	12/31/2005	24,624.24

Annual compensation of $21,945.25 was earned during the 2003 calendar year, $23,455.52 during the 2004 calendar year and $24,624.24 during the 2005 calendar year.

See Temporary Variables, Assignment, and Array Operators to see how array fields behave in expressions.

Transformation expressions allow you to examine and modify values within Service or Salary Definitions.  Basically the expressions “transform” the values of raw and/or computed array data into essential Measurement Period values by using Data Dictionary fields and ProAdmin's Expression Operators. 

When transformation expressions are used, ProAdmin constructs a table, available in the Detailed Results of an Estimated, Final or Dates/Age/Service calculation, to document the computations in the expression. The first two columns of the table are always the values of #DATE and #THIS. Afterward, each variable within the expression is represented by its own column, where each value is the value at the corresponding date in #DATE.  The final column of the table contains the result of the expression.  Values computed during the execution of transformation expressions can be examined, after a calculation has been run, in the Detailed Results  under:

• Service Definition Set – Transformation Expression 
• Salary Definition Set – Transformation Expression

There are two important distinctions:

1. The results of a salary transformation expression must be cumulative values within each measurement period as related to a year. Monthly results represent 1/12 of a year. 

2. The results of a service transformation expression must be cumulative service or service at each date in #DATE.

Here is a little more detail on the calculation and display process:

• The first column, #DATE, is the collection of Calculation Dates from oldest to newest. 

• The second column, #THIS, is the collection of starting values, based on the parameters that correlate to each date in the date column. The values are developed by first examining the data field array specified in the definition and then applying the parameters in the definition.

• The third and subsequent columns are developed through the expressions.

• The final column of the table, called Expression Result, contains the results of the expression and is  used instead of the original values created through #THIS.

Example of a Service Transformation:  

Suppose the company recorded annual hours prior to 2004 incorrectly. They want to grant 1000 hours for years prior to 2004 when the participant worked more than 350 hours, but less than 1000. Otherwise annual hours should be divided by 2000 to determine the amount of service earned in a calendar year. The hours-transformation expression might be:

Code	Comments
A := (#date < 1/1/2004)	&   ; 1 if date is before 2004; 0 if date after 2003
B := (#this > 350 )	&   ; 1 if hours are over 350; 0 if less
C := (#this < 1000 )	&   ; 1 if hours are less than 1000; 0 if more
D := (A x B x C x 1000) #max #this	&   ; hours that will be used
D/2000	; annual service

 

The first two columns in the Transformation Expression table, “Date (#DATE)” and “#THIS,” are provided by ProAdmin using the rules of the Service Definitions. Columns “A,” “B,” “C” and “D” hold the values of the temporary variables. Column “Expression Result” contains the results of the expression and will be inserted as Transformed Hours in the “Expanded Dates” tables of the Service Definition Set.  

 

Date						Expression
(#DATE)	#THIS	A	B	C	D	Results
12/31/2001	1500	1	1	0	1500	0.75
12/31/2002	300	1	0	1	0	0.15
12/31/2003	400	1	1	1	1000	0.50
12/31/2004	1700	0	1	0	1700	0.85
12/31/2005	2080	0	1	0	1000	1.04
12/31/2006	400	0	1	1	400	0.20
12/31/2007	2080	0	1	0	2080	1.04

 Example of a Salary Transformation:

The company includes participants' bonuses in Pensionable Earnings.  In 2012, they made a mistake and included participants' bonuses twice. They need to have Pensionable earnings for 2012 be like all the other years.  The data includes two fields: Pensionable Earnings and Bonus.  The salary transformation expression might be: 

Code	Comments
A:= (#year #date) = 2012	&   ; 1 if date is in 2012; 0 if date is not in 2012
Pay:= #this	&   ; the computed accumulated values of Pensionable Earnings
B:= Bonus	&   ; the annual bonus
Pay #zminus (A * B)	; Pay if the year is not 2012, pay - bonus if year is 2012

The first two columns, “Date (#DATE)” and “#THIS,” are provided by ProAdmin using the rules of the Salary Definition. Columns “A,” “Pay,” and “B” hold the values of the temporary variables. Column “Expression Result” contains the results of the expression and will be inserted as Transformed Salary in the “Expanded Dates” tables of the Salary Definition Set.

Date					Expression
(#DATE)	#THIS	a	Pay	Bon	Result
12/31/2010	25000	0	25000	1000	25000
12/31/2011	26000	0	26000	1000	26000
12/31/2012	28000	1	28000	1000	27000
12/31/2013	28000	0	28000	1000	28000
12/31/2014	29000	0	29000	1000	29000