Skip to Content
2
Jul 18, 2019 at 05:44 AM

Issue with converting Calendar to Fiscal year period through logic script

115 Views

Hello Experts,

We are using logic script to perform YTD calculation, using the logic script provided by Vadim which was very helpful.

*XDIM_MEMBERSET AUDITTRAIL = INPUT_FCAST

*XDIM_MEMBERSET FLOW = F_NONE

*XDIM_MEMBERSET CATEGORY = FORECAST

//*XDIM_MEMBERSET ENTITY = COLUMBIA

*XDIM_MEMBERSET RPTCURRENCY = LC

*XDIM_MEMBERSET INTERCO = I_3RD

*SELECT(%Y%,[YEAR],TIME,[ID] = %TIME_SET%)

*XDIM_MEMBERSET TIME = %Y%.01,%Y%.02,%Y%.03,%Y%.04,%Y%.05,%Y%.06,%Y%.07,%Y%.08,%Y%.09,%Y%.10,%Y%.11,%Y%.12

*WHEN TIME

*IS *

*REC(EXPRESSION = %VALUE%,TIME = %Y%.12)

*ENDWHEN

*XDIM_MEMBERSET TIME = %Y%.01,%Y%.02,%Y%.03,%Y%.04,%Y%.05,%Y%.06,%Y%.07,%Y%.08,%Y%.09,%Y%.10,%Y%.11

*WHEN TIME

*IS *

*REC(EXPRESSION = %VALUE%,TIME = %Y%.11)

*ENDWHEN

*XDIM_MEMBERSET TIME = %Y%.01,%Y%.02,%Y%.03,%Y%.04,%Y%.05,%Y%.06,%Y%.07,%Y%.08,%Y%.09,%Y%.10

*WHEN TIME

*IS *

*REC(EXPRESSION = %VALUE%,TIME = %Y%.10)

*ENDWHEN

... and so on till 01.

The issue with this logic script is that, since our TIME dimension is set up as below, because of the SELECT statement that we have put in the logic script, When we pass a parameter value for Ex: 2013.04, It is converting it to 2014.01, and it writes the data in the time period 2014.01 to 2014.12.

We need the script to write the data in the fiscal year format, i.e. if the TIME value = 2013.04, YTD should be calculated form 2013.04 to 2014.03.

If we pass TIME value = 2013.06, YTD should be calculated from 2013.06 to 2014.03.

Any suggestions would be great!

PS : We tried using TMVL expression and also tried to use the property MUNTHNUMCY, but maybe we are using it incorrectly as we are unable to perform this calculation

**********************

Below is how the TIME dimension is set up in our BPC -

Attachments

time-dim.jpg (128.5 kB)