PRIOR_SERIES_PERIOD_LOOKUP
PRIOR_SERIES_PERIOD_LOOKUP function returns prior series period values. It can also go to forward series period values.
Syntax
PRIOR_SERIES_PERIOD_LOOKUP(seriesOffset, periodIndex, initialValue) > [number]
Arguments
Arguments  Datatype  Description 

seriesOffset  Number  The number of series it has to go back. 
periodindex  Number  The number of periods it has to go back. 
initial Value  Number  The value of first period. 
Remarks
This function can only be used after referring a node for which this calculation needs to be done as shown below in the example.
Seriesoffset and periodindex can also go back to many prior series/periods.
Example
Consider a scenario where you want to create cashflow statement for 3 years and refer the closing balance of prior period as opening balance of current period. In the below table net cash in / out for three periods are given. Calculate the opening cash balance and closing cash balance for all periods in 2020,2021, 2022.
Period  Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sept  Oct  Nov  Dec 

Opening Cash Balance 2020  183439 











Net Cash in/out 2020  10279  10413  14912  9905  15466  7953  14312  12984  12691  12581  10357  31164 
Net Cash in/out 2021  12335  2056  2056  2056  2056  2056  2056  2056  2056  2056  2056  2056 
Net Cash in/out 2022  12828  2138  2138  2138  2138  2138  2138  2138  2138  2138  2138  2138 
To achieve this, the following formula should be written in the [Opening cash Balance] node:
[Closing Cash Balance]
.
PERIOD_LOOKUP
(1,
[Closing Cash Balance]
.
PRIOR_SERIES_PERIOD_LOOKUP
(1,12,183439))
Closing Cash Balance node name is referred in the formula before using PERIOD_LOOKUP & PRIOR_SERIES_PERIOD_LOOKUP so that the calculation is performed for this specific node values. In the PRIOR_SERIES_PERIOD_LOOKUP initial value is given as 183439 which will be considered as first period value for the first series. In the SeriesOffset, the value is given as 1 which will go back to previous series and get the 12th period value from the node Closing Cash Balance. PERIOD_LOOKUP will get the previous period value.
Result
The initial value mentioned in the formula (183439) is taken as the January 2020 opening balance. Closing balance of every month is carried forward as the opening balance for the next month. Closing cash balance of 2020 December will be carried forward as opening balance for January 2021. This happens for all the series.
Last updated