Syntax:
a #MPMIN 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 minimum 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 successively minimize 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 #MPMIN creates an array field (similar to #ARRAY).
When used in a transformation expression, #MPMIN 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:
Minimizing successive array field values in a Data Default
12 #MPMIN Salary
This expression will return the monthly successively minimized amounts of the measurement period values in array field Salary. For a start/stop array field, the stop date is used to determine 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 minimum of the first 2 values in the month; the 3rd value in each month is the minimum 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 minimized 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 minimized within each month (as indicated by the specified measurement period, 12).
Minimizing successive values in 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 minimize 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 minimize 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 #MPMIN 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 same measurement period, be the same.
EOM_dt:= #ENDMTH E_dt & ; round each stop date to the end of the month
EOM_dt #MPMIN AdjSals ; successively minimize values on a monthly basis
Because each salary in the same measurement has the exact same date in the left argument, #MPMIN can minimize successive values appropriately:
Like the first sample expression above (12 #MPMIN Salary), EOM_dt #MPMIN AdjSals successively minimizes the values on a monthly basis: the first value in each month is unchanged; the second value of each month is the minimum of the first 2 values in the month; the 3rd value in each month is the minimum 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 considered to be in the same measurement period. 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 minimize the adjusted salaries in the same measurement period:
Only 2 salaries can be successively minimized because only 2 salaries have the same stop date (i.e., 07/27/19)!
Minimizing successive 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 #MPMIN left argument (e.g., 12 #MPMIN, monthly, but the service/salary measurement period is annual). In the following example, if we assign Sals:= Salary &, then 12 #MPMIN Salary and 12 #MPMIN Sals produce different results. Suppose your start/stop salary array contains these values:
Then, because #DATE is available in a 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 #MPMIN) 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 #MPMIN results. If this is not what you want, then you can replace the zeros with a "large" number (something bigger than your largest reported salary), perform the #MPMIN, and then turn the "large" numbers back into zeros:
Sals:= Salary & ; an array with a calculation date set of number of values
AdjSals:= #IF Sals = 0 #THEN 999999999 #ELSE Sals #ENDIF &; convert zeros into a "large" numberR:= 12 #MPMIN AdjSals &
AdjMPMIN:= #IF R = 999999999 #THEN 0 #ELSE R #ENDIF & ; convert our "large" numbers back into zeros
