Accumulates values within an array on a measurement period basis by successively multiplying values. #MPMUL can be used in Census Specification Data Defaults and Service and Salary Transformation Expressions.
Syntax:
a #MPMUL 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 product 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 #MPMUL creates an array field (similar to #ARRAY).
When used in a transformation expression, #MPMUL 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.
Example:
12 #MPMUL Salary
This expression will return monthly cumulative products of the 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 month is the product of the first 2 values in the month; the 3rd value in each month is the product 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 multiplied 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 successively multiply 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 multiply the values in AdjSals on a monthly basis when we no longer know to what date each salary is associated? (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 #MPMUL where the array has the same number of elements as the right argument. The left argument array indicates to which measurement 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 the same measurement period, be the same.
EOM_dt:= #ENDMTH E_dt & ; round each stop date to the end of the month
EOM_dt #MPMMUL AdjSals ; successively multiple values on a monthly basis
Because each salary in the same measurement has the exact same date in the left argument, #MPMUL can multiply successive values appropriately:
Like the first sample expression above (12 #MPMUL Salary), EOM_dt #MPMUL AdjSals multiplies the values on a monthly basis: the first value in each month is unchanged; the second value of each month is the product of the first 2 values in the month; the 3rd value in each month is the product 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 successively multiplied. 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 multiply values 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)!
Multiplying measurement period values in 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 #MPMUL, monthly, but the service/salary measurement period is annual). In the following example, if we assign Sals:= Salary &, then 12 #MPMUL Salary and 12 #MPMUL Sals produce different results. 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. Also, because 2 salaries had the exact same stop date (7/27/19), in one instance the values were added together (within #MPMUL) 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.
Because the Salary start/stop array field was converted into an array with a calculation date set of number of values using zeros, those zeros had a big impact on the final #MPMUL results. If this is not what you want, then you can replace the zeros with ones, perform the #MPMUL, and then turn the "ones" back into zeros:
Sals:= Salary & ;an array with a calculation date set of number of values
AdjSals:= #IF Sals = 0 #THEN 1 #ELSE Sals #ENDIF & ; convert zeros into a "ones"
R:= 12 #MPMUL AdjSals &
AdjMPMUL:= #IF R = 1 #THEN 0 #ELSE R #ENDIF & ; convert our "ones" back into zeros
