Extracts an array of values at specified dates. 

#GETASOF is available in salary, service, and multiplier transformation expressions, Data Defaults, Benefit Definitions, Accrual Definitions, and SubFormulas.

Syntax:

a #GETASOF b

where:

a is a date, an array of dates, an expression, 'P' (get previous value of b), or 'N' (get next value of b).  In Benefit Definitions, Accrual Definitions and Subformulas, a may also be a component name. 

b is an array of values created by temporary assignment.  In Data Defaults and transformation expressions, b can also be an array field. In transformation expressions, b can be #DATE or #THIS. In Benefit Definitions, Accrual Definitions, and SubFormulas, b can be #DATE or a component. 

#GETASOF works somewhat differently in Data Defaults than in other places because #DATE, the set of calculation dates, is not defined when Data Defaults are evaluated.  In Data Defaults, when b is an array field (and a is not 'P' or 'N'), zero is returned for any dates in a that are not stop dates of a start/stop array field, or any dates in a that are not within the date ranges associated with an effective date array field.  For non-Data Default evaluations (i.e., transformation expressions, Benefit Definitions, Accrual Definitions and SubFormulas), when a date in a is in #DATE (the set of calculation dates), the value of b associated with that date is returned; otherwise zero is returned.

If a is a scalar date (a single value), then a #GETASOF b works just like a #GETVALUE b, and returns the single value of b associated with date a (zero is returned if a is not in #DATE, or if a is not an effective or stop date in field b).

When a is 'P' (previous value of b) or 'N' (next value of b), all the values in b are shifted. For example, if b is an array and contains the values 1 2 3 4 5, then 'P' #GETASOF b returns 0 1 2 3 4 and 'N' #GETASOF b returns 2 3 4 5 0. Note that the previous value of the first item in b is always zero and the next value of the last item is always zero. If b is an array field (effective or start/stop), the values of the array are shifted and returned; the dates are not returned. In the case of transformation expressions, the initial 0 for "P" (previous value) or the final 0 for "N" (next value) may not appear in the Detailed Results  because typically a reduced set of dates is displayed.

When b is a scalar (a single value), in transformation expressions, Benefit Definitions, Accrual Definitions, and SubFormulas, b is expanded into a #DATE number of identical values and then a is used to pull out the appropriate values. In Data Defaults, which are evaluated before a set of calculation dates has been defined, when b is a scalar, a is ignored and the scalar value of b is returned.

The left argument to #GETASOF can be an expression. For example,

in a data default:  (#ENDMTH #START SALARY) #GETASOF SALARY

in a transformation, Benefit Definitions, Accrual Definitions, or subformula expression:  (#ENDMTH #DATE) #GETASOF xyz

However, you will not see the results of these expressions in the expression details found in the Detailed Results. To make testing easier, set a temporary variable equal to the expression (e.g., T_DT:=#ENDMTH #DATE &) and then use that temporary variable as the left argument  (e.g., T_DT #GETASOF xyz). This way you can easily verify that the left argument is correct and that you are retrieving the appropriate value out of the right argument.

​

Example 1 (data default expression)

GFSVC is an effective date array which contains grandfathered service which has been earned at various locations. LOC is an effective date array indicating which locations you have been in. The following default expression creates an effective date array that only contains the grandfathered service from location BCB (=1):

LOC contains

Date	Code	(desc)
1/01/1980	1	BCB
1/01/1990	2	MWC
1/01/1999	1	BCB
1/01/2012	2	MWC

GFSVC contains

Date	Service
1/01/1990	10.00
1/01/1999	9.00
1/01/2012	13.00

GFS_DT:= #START GFSVC & ; effective date of each grandfathered service

SVC := #VALUE GFSVC & ; the amount of each grandfathered service

GFS1_DT:= GFS_DT #DATEMINUS 1D &

 ;   Because of the way the data is reported, we need to ask "what is my location"
 ;   on the day before the grandfathered service became effective! For example, 
 ;   what is my location on 1/1/90? It’s MWC. What is my location on 12/31/89? 
 ;   It’s BCB. Where was the grandfathered service reported on 1/1/90 earned? BCB.

 GFSLOC:= GFS1_DT #GETASOF LOC &

for each grandfathered service, what location was I in

COND:= 1= GFSLOC &

        if you are in location BCB, COND is true (1); otherwise, COND is false (0)

 AdjGFS_DT:= COND * GFS_DT & 

        if you're NOT in BCB, zero out the grandfathered service effective date

12 #ARRAY (AdjGFS_DT, SVC) ; create an effective date array

; NOTE: #ARRAY automatically drops any row with a zero effective date

After applying this data default, the grandfathered service for BCB is:

Date	Service
1/01/1990	10.00
1/01/2012	13.00

 

Example 2 (salary transformation expression):

This example demonstrates how #GETASOF treats start/stop array fields differently than #THIS and temporary variables created by assignment. #GETASOF can only get values at the stop dates in a start/stop array field, but it can find all of the available calculation #DATE dates (e.g., every measurement period end date) in #THIS and in arrays created by assignment. Moreover, if a field is assigned to a temporary variable within a transformation expression, it is automatically projected in order to create a value for each #DATE date. If the assignment occurs during an Estimated Benefit Calculation, the last known value is assumed to remain level in the future regardless of the Projection Assumption salary increase assumptions. If the assignment occurs during a Final Benefit Calculation, zero is used for all future measurement periods because there will be no more salary earned.

Assume salaries are reported on a calendar year basis, the measurement period is calendar year, salaries are projected at 3%, and projected salaries are allocated using calendar days (i.e., the projected salary is: annual salary * (1+#DATE-beginning of year)/days in year.). The actual Salary data, which will be accessed directly in the transformation and which is also accessed in the Salary History, is as follows:

Salary
Start	Stop	Value
7/16/1998	12/31/1998	22983.87
1/1/1999	12/31/1999	52000.00
1/1/2000	12/31/2000	54080.00
1/1/2001	12/31/2001	56243.20

 

The following expressions illustrate three alternative salary transformation results. R1 uses #THIS, R2 uses a temporary variable created from the Salary field and R3 uses the Salary field directly.

DATES:= #DATE & ; every date available to the calculation

EOY_DT:= #ENDMTH (1 #LSTBUSDAY #DATE) & ; round every date to the end of the year

 ;   For example, 7/16/1998 is in #DATE. At 7/16/1998, EOY_DT contains
 ;   12/31/1998 instead of 7/16/1998.

R1:= EOY_DT #GETASOF #THIS & ; get end of year values from #THIS 

 ;   For each date in #DATE, R1 contains the value of #THIS at an end of a year.
 ;   #THIS contains the salaries from the Salary History, after various
 ;   adjustments and projections (as defined in the Salary Definition) have 
 ;   been applied. Since this is an estimate, #THIS would contain salaries
 ;   projected, from the last reported salary, at 3%.

S:= SALARY & ; assign the salary start/stop array field

R2:= EOY_DT #GETASOF S & ; get end of year values from S

 ;   The values in the last measurement period of the SALARY field are used
 ;   to project the salary into the future and then assigned to the temporary
 ;   variable S. The projection of a field in a transformation expression 
 ;   never uses the salary inflation or merit scale. (Even if this Salary 
 ;   History is based on the SALARY field!) Only #THIS can have salary 
 ;   inflation or merit scale! This is one of the many reasons that 
 ;   THIS <> S, S <> SALARY field, and #THIS <> SALARY field!

R3:= EOY_DT #GETASOF SALARY & 

 ;   The left argument to #GETASOF is an array field, so #GETASOF only looks at
 ;   the stop dates in that field to get values. Since #DATE always contains 
 ;   many more dates than just the stop date in the array field, most of the
 ;   values in R3 will be zero!

Here are the results of the above transformations for the illustrative Salary field:

#DATE	EOY_DT	#THIS	R1	S	R2	SALARY	R3
…
12/31/1997	12/31/1997	0.00	0.00	0.00	0.00	0.00	0.00
1/1/1998	12/31/1998	0.00	22983.87	0.00	0.00	0.00	22983.87
7/16/1998	12/31/1998	0.00	22983.87	0.00	0.00	0.00	22983.87
12/31/1998	12/31/1998	22983.87	22983.87	22983.87	22983.87	22983.87	22983.87
12/31/1999	12/31/1999	52000.00	52000.00	52000.00	52000.00	52000.00	52000.00
6/30/1999	12/31/2000	0.00	54080.00	0.00	0.00	0.00	54080.00
12/31/2000	12/31/2000	54080.00	54080.00	54080.00	54080.00	54080.00	54080.00
12/31/2001	12/31/2001	56243.20	56243.20	56243.20	56243.20	56243.20	56243.20
1/17/2002	12/31/2002	2698.13	57930.50	2619.55	56243.20	0.00	0.00
11/13/2002	12/31/2002	50312.24	57930.50	48846.83	56243.20	0.00	0.00
12/31/2002	12/31/2002	57930.50	57930.50	56243.20	56243.20	0.00	0.00
12/31/2003	12/31/2003	59668.41	59668.41	56243.20	56243.20	0.00	0.00
…

Example 3:  Please see the FAQ "Adjusting cash balance values for distributions and repayments" for an example of using #GETASOF in an accrual definition basis formula.

Examples of how NOT to use #GETASOF in a Data Default

1. If you're defaulting a scalar, the following default will abort because too many values are returned (the default expression for a scalar should return a single value):

2. If you're defaulting a numeric start/stop array, the following will abort because you didn't create an array (just a set of values without start and stop dates):

3. If Salary is a numeric start/stop array with 5 rows and Bonus is a start/stop array with 4 rows, then this default will abort because you are trying to create an array with 5 start dates, 5 stop dates, and 4 values (from the Bonus array)!

       B_DT:= #START Salary &

       E_DT:= #STOP  Salary 

       VB  := #VALUE Bonus & 

       11 #ARRAY (B_DT,E_DT,VB)

In ProAdmin, an array is a date-dependent set of values which may be codes, numbers, or dates. The following are arrays that are typically encountered and used in ProAdmin:

• Array fields
• #THIS
• #DATE
• The results of a Benefit Definition, Benefit Formula Component, and Accrual Basis Component (available in higher level expressions, such as Accrual Definitions, Subformulas, Benefit Expressions)
• Some temporary variables in service & salary transformation expressions, and in Accrual Definition, Subformula, and Benefit Formula expressions. In this case of temporary values, the effective date for each value is #DATE and the temporary variable holds the values of the array.  Here are some examples of temporary variables definitions that do and do not create arrays:

A:= #ENDMTH #DATE & ; array
B:= #THIS >= 1000 & ; array
C:= SALARY  & ; array (salary is a start/stop array field*) 

D:= 1       & ; scalar (i.e., a single value)
E:= DOB     & ; scalar (i.e., a single value)

 

*You should also see assignment using array fields in transformation expressions and assignment using array fields in data default expressions.

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.