Accumulates values within an array on a measurement period basis by successively summing the values in each measurement period. #MPSUM can be used in Census Specification Data Defaults and Service and Salary Transformation Expressions.
Syntax:
a #MPSUM 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 field, #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 total 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), 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 accumulate 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, the right argument must be an array field, and #MPSUM creates an array field (similar to #ARRAY).
When used in a transformation expression, #MPSUM 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:
Accumulating array fields in a Data Default
12 #MPSUM Salary
This expression will return the monthly successively accumulated 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 sum of the first 2 values in the month; the 3rd value in each month is the sum of the first 3 values in the month; etc.
The example below illustrates how the given values in a Salary start/stop array field are successively accumulated 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 accumulated within each month (as indicated by the specified measurement period, 12).
Accumulating 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 accumulate 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 accumulate the values in AdjSals on a monthly basis 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 #MPSUM AdjSals ; accumulate on a monthly basis
Because each salary in the same measurement has the exact same date in the left argument, #MPSUM can accumulate the values appropriately:
Like the first sample expression above (12 #MPSUM Salary), EOM_dt #MPSUM AdjSals accumulates the values on a monthly basis: the first value in each month is unchanged; the second value of each month is the sum of the first 2 values in the month; the 3rd value in each month is the sum of the first 3 values 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 accumulated. 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 accumulate the adjusted salaries in the same measurement period:
Only 2 salaries can be accumulated because only 2 salaries have the same stop date (i.e., 07/27/19)!
Accumulating arrays in transformation expressions
Whether you start with an array field or create an array by assignment, in a transformation expression, they are treated the same unless you have multiple values with the same stop date or the underlying measurement period of the Service or Salary Definition is different than the #MPSUM left argument (e.g., 12 #MPSUM, monthly, but the service/salary measurement period is annual). For example, if we assign Sals:= Salary &, then 12 #MPSUM Salary and 12 #MPSUM Sals produce the same result; unless you have multiple values with the same stop date (in this example, the service/salary's measurement period is monthly). For example, suppose your start/stop salary array contains these values (note that 2 values have a 7/27/19 stop date):
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 was added to the set of calculation dates. Also, because 2 salaries had the exact same stop date (7/27/19), in one instance the values were added together (within #MPSUM) and in the other (Sals:= Salary &) the last value with the same stop date was used. 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.
