Monthly periods crossing multiple years at logic run-time

[former_member591760]

I have a package that includes a prompt for a time period.  It also contains a section where some time periods are hard coded directly in the script and I am looking for a way to eliminate the manual update required for those time periods.  As of right now, March is our active month for posting so I would be choosing 2016.MAR when running the package.  The periods required in the "manual section" are the 12 monthly periods ending with 2016.MAR, so in this scenario I would need from 2015.APR through 2016.MAR

I can use the line below to get the periods from the current year but how can I do the same for the months that are part of the prior year?

*SELECT(%CURYRPRDS%,"[ID]", "TIME", "[YEAR] = '%TIME_FY%' AND [TIMEID] <= '%CURTIMEID%' AND [LEVEL] = 'MONTH'  ")

For reference, our year runs from OCT to SEP and we have a "monthnum" property in our time dimension with values from 1-12 for each month.

[former_member186498]

Hi Pablo,

instead of TMVL you can use Time-Shift Instructions PRIOR or NEXT with offset (see the help)

Regards

     Roberto

[former_member591760]

Hi Roberto,

I tried the following and it affects the period I'm passing from Data Manager (2016.MAR) along with 2015.APR.  Do you have an example I could follow?

*XDIM_MEMBERSET TIME=%TIME_SET%, PRIOR(1), PRIOR(2), PRIOR(3), PRIOR(4), PRIOR(5), PRIOR(6), PRIOR(7), PRIOR(8), PRIOR(9), PRIOR(10), PRIOR(11)

[JohnL]

You could also try using %YEAR% and %YEAR%(-1) in your select.

Thanks,

[former_member591760]

I'm trying this now but can't get it to work.  My thought was to check the time period supplied at run-time to get "monthnum" value from the time dimension.  If the period is the 12th month, I need to pull all members from the current year which I can do easily.  If the period is not the 12th month, I need to pull from both the current year and the prior year so I'm trying to test that scenario using 2016.MAR which is monthnum 6.  This is what I'm using for identifying the time periods followed by an xdim statement to assign the time values.

*WHEN TIME.MONTHNUM

  *IS <>12

    *SELECT(%CY_PRDS%, "[ID]", "TIME", "[YEAR]='%TIME_FY%' AND [MONTHNUM] <= '%TIME_MONTH_NO%' AND [LEVEL] = 'MONTH' ")

    *SELECT(%PY_PRDS%, "[ID]", "TIME", "[YEAR]='%TIME_FY%(-1)' AND [MONTHNUM] >= '%TIME_MONTH_NO%' AND [LEVEL] = 'MONTH' ")

*ELSE

  // Pull all current year periods

*ENDWHEN

*XDIM_MEMBERSET TIME = %CY_PRDS%,%PY_PRDS%

The first select statement should assign 6 months but it's affecting 9 periods.  I assumed it would only apply to 1-6, but it is including 10, 11, and 12.  In the dimension table, the monthnum value is formatted as general, would that cause any issues?  The second select statement says "No records returned by *SELECT(%PY_PRDS%..." so I believe there is an issue trying to adjust the year using (-1).

[former_member591760]

David - I wasn't aware of TMVL so I will research and try it out later this afternoon.

Robert - It's a script with a number of different commits and most of the commits use the monthly period specified in the Data Manager prompt.  For the commit I'm looking to change, we update and hard code the 12 monthly periods before running the script.  So every month I have to manually update the line below by deleting the first period and adding the current period to the end.  It's not a big deal to update but I want to have an administrative person run the script and would like to automate whatever I can.

*XDIM_MEMBERSET TIME = 2015.APR,2015.MAY,...2016.FEB,2016.MAR

Thank you

PS - Does anyone know what happened to the document that was linked here:

[former_member186498]

Hi Pablo,

can you please share package, script or stored proc and input file?

Thanks

     Roberto

[Former Member]

Have you tried to use TMVL(%OFFSET%,%TIME_SET%) where %OFFSET% will hold the following : -1, -2, -3, -4... -11 ?

You can then use this in a *REC and/or *GET statement to read / write a time period offset based on the time period set in the prompt.

[JohnL]

Hi David,

TMVL is not supported in BPC MS.

Thanks,