When loading YTD data as periodic data in conjunction with In Batch
Accumulation, you need to be aware of how periodic data is calculated,
especially when leading period in the input file is not the first period of the
year. All the data is summed in the batch before any subtractions.
The example assumes that January is the first period of the year. The
data file contains April, May, and June YTD data, while the database
FinLoc/FinPer table contains January, February, and March data.
| Database
|
January
|
February
|
March
|
| Finloc (Periodic)
|
10
|
11
|
12
|
|
April
|
May
|
June
|
| YTD-row1
|
20
|
25
|
34
|
| YTD-row2
|
26
|
35
|
41
|
| Periodic
|
13
|
14
|
15
|
- April periodic =
(20+26)-10-11-12=13
- May periodic =
(35+25)-(26+20)=14
- June periodic
=(41+34)-(35+25)=15
When using YTD_DATA with Accumulation option, there may be some
occasions that you want to turn off subtraction data in the database,
especially when leading period in the input file is not the first period of the
year. For example, suppose you have two data files containing the same subcube
rows in each of the files. During the first load, the leading periodic data is
obtained by subtracting previous periods in the database from the leading
period in the input file. In the subsequent loads, you might want to turn off
the subtraction, so that the subtraction occurs only once. This is an example
demonstrating this scenario. To turn off the subtraction, use
SUBTRACT_LEADING_PERIODS=OFF in the configuration file.
| Database
|
January
|
February
|
March
|
April
|
May
|
June
|
| Finloc (Periodic)
|
10
|
11
|
12
|
NULL
|
NULL
|
NULL
|
|
April
|
May
|
June
|
| YTD-file 1
|
20
|
25
|
34
|
| Periodic
|
-13
|
5
|
9
|
- April periodic =
0+20-10-11-12=-13
- May periodic = 0+25-20=5
- June periodic = 0+34-25=9
|
April
|
May
|
June
|
| YTD-file 2
|
26
|
35
|
41
|
| Periodic (After second load without turning off subtraction)
|
-20
|
14
|
15
|
- April periodic =
-13+26-10-11-12=-20
- May periodic = 5+35-26=14
- June periodic =9+41-35=15
|
April
|
May
|
June
|
| YTD-file 2
|
26
|
35
|
41
|
| Periodic (After second load by turning off subtraction)
|
13
|
14
|
15
|
- April periodic = -13+26=13
- May periodic = 5+35-26=14
- June periodic = 9+41-35=15