Home > FAQ > Data Manipulation > Editing Arrays > Allocate reported values based on anniversary years

Allocate reported values based on anniversary years

Suppose you need to calculate service or salary on an anniversary year basis, but your data (e.g., salary or hours) is reported on a different basis (e.g., calendar year, plan year, monthly, biweekly, weekly, or "randomly"). How do you reallocate the reported data so it is used/recognized in the anniversary year it was "earned"?

This example assumes that the anniversary year is based on the date of hire (e.g., 7/17/1998) and that salaries are reported on a calendar year basis (chosen to minimize the accompanying sample expression result displays below).

The data default expression below will work with any begin anniversary date (even leap dates, e.g., 2/29/2000) and with any start/stop array as long as there are at most 0 or 1 anniversary dates between the start and stop date of any reported value.

We only need to (change) reallocate reported salaries when an anniversary date lies between their start and stop date: reallocate the salary for the period [startdate,stopdate] between [startdate,end of previous anniversary year] and [begin of current anniversary year, stopdate] using calendar days:

When there isn't a begin anniversary date between a pair of start/stop dates, then nothing is changed.

The easiest place to do this is in a Data Default.

First thing you need to do is create a new start/stop array Data Dictionary field (e.g. AnnivSalary) and create a data default for it. In the data default, make sure you replace all values, whether or not missing. This is to ensure that if the original salary data is modified (e.g., loaded from the database or XML), then the AnnivSalary default will be applied and it will reallocate your modified salaries to the appropriate anniversary year (i.e., Salary and AnnivSalary are consistent, in sync).

In the data default expression, grab the start dates, stop dates, and values from the datafield containing the original salary data  so we can individually work with them (see B_dt, E_dt, V).

Next we need a begin anniversary date (e.g., anniv_dt:= DOH) so we can calculate the anniversary date coincident or preceding each salary stop date (e.g., AnnivBeforeStop_dt). 

With these begin anniversary dates we can determine if there's an anniversary date between each reported salary's start and stop dates (see flag), and if there is, we can reallocate its reported value to the appropriate anniversary year (i.e., the year in which it was "earned").

Create a copy of the original array (see B1_DT, E1_DT, V1) and for any start/stop date pair with an anniversary begin date between them, we’ll change the stop date to the anniversary year end date and prorate its reported salary. For those start/stop date pairs without a begin anniversary date between them, we do nothing (those rows will be identical to the rows in the original array).

Create a copy of the original array (see B2_DT, E2_DT, V2) and for any start/stop date pair with an anniversary begin date between them, we’ll change the start date to the begin anniversary year date and then prorate the reported salary accordingly. For those start/stop date pairs without an anniversary begin date between them, we'll set those rows equal to zeros (i.e., we'll set their B2_DT's, E2_DT's, V2's to zero) so that those rows will be dropped: those rows were accounted for and unchanged, from the original array, in the first array we created (see B1_DT, E1_DT, V1).


B_DT:= #START salary & ; start dates
E_DT:= #STOP  salary & ; stop dates
V:=    #VALUE salary & ; values (i.e., the reported salaries)


anniv_dt:= DOH & ; any beginning of the anniversary year date
YR_DIF:= E_DT #YEARDIF anniv_dt & ; diff in years+(months/12)+(days/366)

AnnivBeforeStop_dt:= anniv_dt #DATEPLUS ((#FLOOR YR_DIF)*1Y) &
; the begin anniversary date coincident with or preceding each stop date

flag:= (B_DT < AnnivBeforeStop_dt) #AND (E_DT >= AnnivBeforeStop_dt) &
; Is the begin of the anniversary year between the start/stop date?
; {is it in (startdate,stopdate]}


B1_DT:= B_DT &

E1_DT:= #IF flag ; is a begin anniversary date between the start & stop dates?
        #THEN AnnivBeforeStop_dt - 1
        ; use the preceding end of anniversary year as the stop date
        #ELSE E_DT ; use actual stop date
        #ENDIF &

V1:= V * (1 + E1_DT - B1_DT) #DIV (1 + E_DT - B_DT) &
; use calendar days to prorate value V
;   returns a fraction of V for [start, end of anniv] if beg of anniv between
;   start & stop dates, otherwise V (because the fraction is 1) if beg of anniv
;   NOT between start & stop dates.

B2_DT:= flag * AnnivBeforeStop_dt &
; if a begin anniversary date is between the start & stop dates, use
; beg of anniv as the start date; otherwise use zero to drop the row

E2_DT:= flag * E_DT & ; if don't need the value, use zero to drop row
V2:= flag * V * ((1 + E2_DT - B2_DT) #DIV (1 + E_DT - B_DT)) &
; use calendar days to prorate value V
;   returns a fraction of V for [boy of anniv, stopdate] if beg of anniv between
;   start & stop dates, otherwise ZERO if beg of anniv is not between start & stop
;   dates. These zeros will be dropped because the corresponding start & stop dates
;   (in B2_DT and E2_DT) have been set to zero)


11 #ARRAY(B1_DT,E1_DT,V1,B2_DT,E2_DT,V2)
; create a start/stop array: (start,stop,values)
; NOTE: This array is built from 2 sets of (start,stop,values)! The first set
;       (B1_DT,E1_DT,V1) creates as many rows as are in the original array
;       (Salary). While the second set (B2_DT,E2_DT,V2) contributes the same
;       number of rows, but some of those rows may contain start or stop dates
;       that have been set to zero, so those rows will be dropped from the
;       final array (AdjustedSalary).

 

Suppose the date of hire is 7/17/1998 and the salary is

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
1/1/2002 1/31/2002 4874.41
2/1/2002 2/28/2002 4874.41
3/1/2002 3/31/2002 4874.41
4/1/2002 4/30/2002 4874.41
5/1/2002 5/31/2002 4874.41
6/1/2002 6/30/2002 4874.41
7/1/2002 7/31/2002 4874.41
8/1/2002 8/31/2002 4874.41
9/1/2002 9/30/2002 4874.41

 

Then the details of the expression are

B_DT E_DT V anniv_dt YR_DIF ANNIVBEFORESTOP_DT FLAG
7/16/1998 12/31/1998 22983.87 7/17/1998 0.456284 7/17/1998 1
1/1/1999 12/31/1999 52000.00 7/17/1998 1.456284 7/17/1999 1
1/1/2000 12/31/2000 54080.00 7/17/1998 2.456284 7/17/2000 1
1/1/2001 12/31/2001 56243.20 7/17/1998 3.456284 7/17/2001 1
1/1/2002 1/31/2002 4874.41 7/17/1998 3.540984 7/17/2001 0
2/1/2002 2/28/2002 4874.41 7/17/1998 3.617486 7/17/2001 0
3/1/2002 3/31/2002 4874.41 7/17/1998 3.704918 7/17/2001 0
4/1/2002 4/30/2002 4874.41 7/17/1998 3.786885 7/17/2001 0
5/1/2002 5/31/2002 4874.41 7/17/1998 3.871585 7/17/2001 0
6/1/2002 6/30/2002 4874.41 7/17/1998 3.953552 7/17/2001 0
7/1/2002 7/31/2002 4874.41 7/17/1998 4.038251 7/17/2002 1
8/1/2002 8/31/2002 4874.41 7/17/1998 4.122951 7/17/2002 0
9/1/2002 9/30/2002 4874.41 7/17/1998 4.204918 7/17/2002 0

 

B1_DT E1_DT B2_DT E2_DT V1 V2
>7/16/1998 7/16/1998 7/17/1998 12/31/1998 136.00 22847.87
>1/1/1999 7/16/1999 7/17/1999 12/31/1999 28065.75 23934.25
1/1/2000 7/16/2000 7/17/2000 12/31/2000 29256.39 24823.61
1/1/2001 7/16/2001 7/17/2001 12/31/2001 30355.92 25887.28
1/1/2002 1/31/2002 0 0 4874.41 0.00
2/1/2002 2/28/2002 0 0 4874.41 0.00
3/1/2002 3/31/2002 0 0 4874.41 0.00
4/1/2002 4/30/2002 0 0 4874.41 0.00
5/1/2002 5/31/2002 0 0 4874.41 0.00
6/1/2002 6/30/2002 0 0 4874.41 0.00
7/1/2002 7/16/2002 7/17/2002 7/31/2002 2515.82 2358.59
8/1/2002 8/31/2002 0 0 4874.41 0.00
9/1/2002 9/30/2002 0 0 4874.41 0.00

 

And 

11 #ARRAY (B1_DT,E1_DT,V1,B2_DT,E2_DT,V2)
will create this start/stop array

 

SalaryAnniv
Start Stop Value
7/16/1998 7/16/1998 136.00
7/17/1998 12/31/1998 22847.87
1/1/1999 7/16/1999 28065.75
7/17/1999 12/31/1999 23934.25
1/1/2000 7/16/2000 29256.39
7/17/2000 12/31/2000 24823.61
1/1/2001 7/16/2001 30355.92
7/17/2001 12/31/2001 25887.28
1/1/2002 1/31/2002 4874.41
2/1/2002 2/28/2002 4874.41
3/1/2002 3/31/2002 4874.41
4/1/2002 4/30/2002 4874.41
5/1/2002 5/31/2002 4874.41
6/1/2002 6/30/2002 4874.41
7/1/2002 7/16/2002 2515.82
7/17/2002 7/31/2002 2358.59
8/1/2002 8/31/2002 4874.41
9/1/2002 9/30/2002 4874.41