cancel
Showing results for 
Search instead for 
Did you mean: 

Reading previous period value is logic

Former Member
0 Kudos
153

Hi,

There's a need to read previous period value is logic for some processing, can you please help me out how this can be achieved. I have a selection for time in the package called %TIME_DIM% and I am running for the current period. I have tried following options

First

*WHEN XYZ
*IS "ABC"
*REC = (FACTOR=GET(TIME=PRIOR), SOMEDIM=VALUE)
*ENDWHEN

When above code executed with current period (no record exists for current period) nothing is read.

Second

*XDIMMEBERSET TIME = PRIOR, %TIME_DIM%
*WHEN XYZ
*IS "ABC"
*REC = (FACTOR=GET(TIME=PRIOR), SOMEDIM=VALUE)
*ENDWHEN

In the above case no record is selected and surprisingly the select statement fired (got it form the log) is for the last time period maintained in the system - 1. So if I have time dimension members till 2020.DEC this picks up 2020.NOV. I didn't understand why?

Would appreciate help on this.

Thanks

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Anand,

It is not very clear what you are trying to achieve here.

Perhaps you already have this, but below some explanation about special time selections that you can use in SQL logic.

The time shift instructions

To simplify the calculation of leads and lags in financial reporting applications, the following new instructions have been implemented for SQL-based logics:

PRIOR

NEXT

BASE

FIRST

The instructions PRIOR and NEXT support an optional numeric parameter. This parameter represents the number of time periods by which the current period must be shifted. If omitted, the functions will assume a time shift of 1 period (forward or backwards). Negative values are accepted (A negative value for a NEXT function corresponds to a positive value for a PRIOR function and vice-versa).

Examples:

TIME=NEXT // In a monthly application this means next month

TIME=PRIOR(3) // Three periods backwards

TIME=NEXT(-3) // Same as PRIOR(3)

The keyword BASE always represents the last period of prior fiscal year. When the fiscal year is a normal calendar year and the frequency is monthly, the base period of 2004.JUN is 2003.DEC.

The instruction BASE can be useful in YTD applications, where the opening balances need to be retrieved from the last period of prior year.

The keyword FIRST always represents the first period of the current fiscal year. When the fiscal year is a normal calendar year and the frequency is monthly, the base period of 2004.JUN is 2004.JAN.

In case the time shift goes past the boundaries of the TIME dimension, these time shift functions will return no period.

These functions can be used in four ways:

- To re-direct the destination period in a *REC statement

Example 1: *REC(TIME=NEXT)

Example 2: *REC(TIME=BASE)

- To retrieve a value from a different period in a *REC statement

Example 1: *REC(FACTOR=GET(TIME=PRIOR(3))

Example 2: *REC(FACTOR=GET(TIME=BASE)

- To add periods to the selected data region in a XDIM_MEMBERSET statement

Example: *XDIM_MEMBERSET TIME=PRIOR, %TIME_SET%

In this example, if the first modified period is 2004.APR, the instruction PRIOR will add 2004.MAR to the region to process).

- When the keywords PRIOR, FIRST or BASE are added to a XDIM_MEMBERSET instruction, the time period PRIOR, FIRST or BASE can be also evaluated in a WHEN / ENDWHEN structure, like in the following example:

*WHEN TIME

*IS PRIOR

// ignore

*ELSE

*REC(u2026)

*ENDWNHEN

In presence of an XDIM_MEMBERSET containing the PRIOR keyword, like in the above example, the WHEN structure here shown will recognize 2004.MAR as PRIOR period.

Following is an example of logic that performs a carry-forward of account ACCREC, while adding to it the periodic amount from EXTSALES.

*XDIM_MEMBERSET TIME=PRIOR,%SET%,%PREFIX%.DEC

*CALC_EACH_PERIOD

*WHEN TIME

*IS PRIOR

*WHEN ACCOUNT

*IS ACCREC

*REC(ACCOUNT=u201DOPEACCRECu201D,TIME=NEXT)

*ENDWHEN

*ELSE

*WHEN ACCOUNT

*IS EXTSALES

*REC(FACTOR=-1,ACCOUNT="OPEACCREC",TIME=NEXT)

*REC(FACTOR=-1,ACCOUNT="ACCREC")

*IS OPEACCREC

*REC(ACCOUNT=u201DACCRECu201D)

*REC(ACCOUNT=u201DACCRECu201D,TIME=NEXT)

*ENDWHEN

*ENDWHEN

Hope this helps,

Alwin

Former Member
0 Kudos

Alwin, It reminds me of something

Former Member
0 Kudos

Thanks for a reply everyone,

What I want to achieve is based on a user entry for time in package read the data from previous period and write it to the current period and a new account.

My user entry variable on the package for time is %TIME_DIM%. If user triggers the package and enters 2007.MAY I want to read data from 2007.APR and write it to 2007.MAY into another account. I have put in the selection for the accounts and it works fine only time thing is confusing.

thanks

Former Member
0 Kudos

Anand,

I would try it with the lines below:

*XDIM_MEMBERSET TIME=PRIOR,%TIME_SET%
*XDIM_MEMBERSET ACCOUNT=<ALL>

*WHEN TIME
*IS PRIOR
*WHEN ACCOUNT.MAPPING
*IS <> ""
    *REC(ACCOUNT=ACCOUNT.MAPPING,TIME=NEXT)
*ENDWHEN
*ELSE

Former Member
0 Kudos

sorry, something went wrong when pasting the code, below the correct code

*XDIM_MEMBERSET TIME=PRIOR,%TIME_SET% *XDIM_MEMBERSET ACCOUNT=<ALL> *WHEN TIME *IS PRIOR *WHEN ACCOUNT.MAPPING *IS <> "" *REC(ACCOUNT=ACCOUNT.MAPPING,TIME=NEXT) *ENDWHEN *ELSE

Former Member
0 Kudos

Last try


*XDIM_MEMBERSET TIME=PRIOR,%TIME_SET%
*XDIM_MEMBERSET ACCOUNT=<ALL>
 
*WHEN TIME
*IS PRIOR
*WHEN ACCOUNT.MAPPING
*IS  <> ""
    *REC(ACCOUNT=ACCOUNT.MAPPING,TIME=NEXT)
*ENDWHEN
*ELSE

Former Member
0 Kudos

Anand,

One detail of the code is going wrong when pasting it:

the line:

*is ""

should be:

*is <> "" (smaller than and bigger than signs between *is and "")

Alwin

Former Member
0 Kudos

Hi!

Here is another thing I did this week:


*XDIM_MEMBERSET ACCOUNT=Descendants([A10510],99,LEAVES)   //select only the descendants of the account A10510 (if you can filter you should!)
*XDIM_MEMBERSET TIME=PRIOR, %TIME_SET%                               // select BASE (for end of previous fiscal yea) and time_set BASE being 2007.dec for 2008.mar for expl..

*WHEN ACCOUNT 
    *IS *                                                      //take all the accounts
            *WHEN TIME
                 *IS PRIOR                                // and take the data from the previous period so jan if you are in feb...
                 *REC(ACCOUNT="A10000",TIME=%TIME_SET%)               //and put it in the Account A10000 current period
            *ENDWHEN
*ENDWHEN

*COMMIT

I hope it is clear for you now

Nic

Former Member
0 Kudos

Alwin,

I think the NEXT is wrong... because if you are in June, it will take data from may and put it in july... I think he wants the data in June.

And what about the ENDWHEN??

So it should be:

*XDIM_MEMBERSET TIME=PRIOR,%TIME_SET%

*XDIM_MEMBERSET ACCOUNT=<ALL>

*WHEN TIME

*IS PRIOR

*WHEN ACCOUNT.MAPPING

*IS ""

*REC(ACCOUNT=ACCOUNT.MAPPING,TIME=%TIME_SET%)

*ENDWHEN

*ENDWHEN

Alwin, I also posted smtg in the Forum, if you could have a look please...

Nic

Former Member
0 Kudos

Thanks a lot guys.

I was keying in the wrong variable name in the following statement

*XDIM_MEMBERSET TIME = PRIOR,%TIME_SET%

Instead of %TIME_SET% I was using the variable from package which is %TIME_DIM%, didn't know that the value entered in dimension selection is stored in %DIMNAME_SET%

You all are the best

thanks

Former Member
0 Kudos

Nicolas,

As per your code, %TIME_SET% should be replaced with current period while generating the record? If this is true then what would happen if you were passing more than one values to variable %TIME_SET% (say Jan - May 2008)?

Thanks, Rahul

Answers (2)

Answers (2)

Former Member
0 Kudos

Nicolas,

I am indeed missing an endwhen instruction in the code that I pasted.

I think that the code you pasted will work as well, In fact it should do the same thing as the code that I posted. I don't have any preference on what is the best.

The next instruction in the *rec works from the original record and not from the time_set instruction, so it should work (I am not the ultimate logic guru, but this is how I have done this in the past)

Alwin

Former Member
0 Kudos

Hi!

PRIOR means the previous period.

I am sorry but, I have difficulties in understanding what you would like the logic to do. Could you write it down please, I will see if I can help you.

Nic