0

# YTD to periodic data calculation in different model

Aug 24, 2017 at 10:23 AM

73

Former Member

Hi Friends,

We are on BPC 10.0 NW

Trying to move GL balances(YTD) in legal consolidation application to other application (financial type- periodic).

Script written-

```*SELECT(%YR%,[YEAR],CATEGORY,"[ID] = '%CATEGORY_SET%'")

*SELECT(%MTH%,[STARTMNTH],CATEGORY,"[ID] = '%CATEGORY_SET%'")

*SELECT(%TM1%,ID,TIME,"[YEAR] = %YR% AND [MONTHNUM] = %MTH%")

//*XDIM_MEMBERSET TIME = %TM1%

*SELECT(%ACC%,[ID],ACCOUNT,"[ACCTYPE] = 'AST' AND [ACCTYPE] = 'LEQ' AND CALC = 'N'")

*XDIM_MEMBERSET TIME = %TM1%,TMVL(-1,%TM1%)

*XDIM_MEMBERSET ACCOUNT = %ACC%

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%

//*WHEN_REF_DATA = MASTER_DATA

*DESTINATION_APP = CONSOL_PER

*WHEN TIME

*IS %TM1%

*WHEN TIME.MONTHNUM

*IS "12"

*ELSE

*REC(EXPRESSION = %VALUE%-[TIME].[TMVL(-1,%TM1%)],TIME = %TM1%)

*ENDWHEN

*ENDWHEN```

Now the issue that we are facing is doing the subtraction when the flow between two periods are different.

E.g, Our Financial Year ends on 31st March so we have 2017.12 closing period & then 2018.01 as opening.

AuditTrail Flow Time Amount

period 2017.12 is having data in flow 100, 120 & 170

but flow 2018.01 have data only in 100 & 120.

Result of this script is

D_UPLOAD_OP 100 2018.01 1200 (i.e, 3200-2000)

D_UPLOAD 120 2018.01 100 (i.e, 300-200)

The data of Flow in 170 is not getting captured in the subtraction.

Desired output should be-

D_UPLOAD_OP 100 2018.01 1200 (i.e, 3200-2000)

D_UPLOAD 120 2018.01 100 (i.e, 300-200)

D_UPLOAD 170 2018.01 -1000 (i.e, 0 - 1000)

PS- Clearly visible example is attached with this thread.

example-scrren.jpg

Regards,

Nukul

example-scrren.jpg (89.8 kB)

Vadim Kalinin Aug 24, 2017 at 11:26 AM
0

Show 3 Share
Former Member

I have attached the script in Word, please have a look and help in resolving the issue.

Former Member

I do not need a picture of your script!

Please correct original post to have proper lines of script.

and provide other required info.

Former Member

Vadim Kalinin Aug 24, 2017 at 12:17 PM
0

OK, now the script is readable!

But you did not provided extra info mentioned in my blog! I have no time to ask each question individually!

In general: this line is wrong

`*REC(EXPRESSION = %VALUE%-[TIME].[TMVL(-1,%TM1%)],TIME = %TM1%)`

The script has to be:

```*SELECT(%YR%,[YEAR],CATEGORY,"[ID] = '%CATEGORY_SET%'")
*SELECT(%MTH%,[STARTMNTH],CATEGORY,"[ID] = '%CATEGORY_SET%'")
*SELECT(%TM1%,ID,TIME,"[YEAR] = %YR% AND [MONTHNUM] = %MTH%")
*SELECT(%ACC%,[ID],ACCOUNT,"[ACCTYPE] = 'AST' AND [ACCTYPE] = 'LEQ' AND CALC = 'N'")

*XDIM_MEMBERSET ACCOUNT = %ACC%
*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_MEMBERSET TIME = %TM1%,TMVL(-1,%TM1%) //2017.09,2017.08

*DESTINATION_APP = CONSOL_PER
*WHEN TIME
*IS %TM1% //2017.09
*REC(EXPRESSION = %VALUE%,TIME = %TM1%) // add value
*IS TMVL(-1,%TM1%) //2017.08
*WHEN TIME.MONTHNUM
*IS<>12 //If DEC - skip; DEC will be if %TM1% is JAN
*REC(EXPRESSION = -%VALUE%,TIME = %TM1%) //subtract value
*ENDWHEN
*ENDWHEN```
Show 12 Share
Former Member

Sir,

updated the question with example in table and the desired output from the script.

Former Member

I have already provided you a correct script.

Former Member

Sir,

Unfortunately this script is also giving the same result, data in Flow 170 getting ignored with this also.

Former Member

Former Member

Sir,

Please find attached the screenshot of Time Dimension.time-dim-screen.jpg

time-dim-screen.jpg (115.0 kB)
Former Member

Incorrect TIME dimension setup!

MONTH_NUM=1 has to be JAN etc!

Please explain what calendar months are corresponding to ID's

2018.01 is calendar 2018.04?

Also in your original post you are talking about data from 2017 to be subtracted from data of the first month of 2017. But it's not YTD!

Former Member

Sir,

Ours is April to March Financial year, so 2018.01 is April not JAN.

We want the subtraction of data in the way i attached in example-screen.jpg.

Each period is storing YTD data as shown in the example, so for deriving periodic values i want to just do the subtraction of current period(2018.01) less previous period 2017.12.

The same exercise needs to be done next month when current period is 2018.02 & previous period is 2018.01.

example-scrren.jpg

example-scrren.jpg (89.8 kB)
Former Member

Sorry, PROPERTY values in TIME dimension require correct values!

And it's mandatory to have PERIOD property of the first month of the year to be JAN, not APR! Same for other periods.

If you need real calendar month - create another property like CALPERIOD and fill it like you want.

"Each period is storing YTD data as shown in the example, so for deriving periodic values i want to just do the subtraction of current period(2018.01) less previous period 2017.12." - you are talking not about YTD calculation. YTD figures are accumulated starting from the first month of the year, not considering previous year.

To implement the logic you want look on the following code:

```*SELECT(%YR%,[YEAR],CATEGORY,"[ID] = '%CATEGORY_SET%'")
*SELECT(%MTH%,[STARTMNTH],CATEGORY,"[ID] = '%CATEGORY_SET%'")
*SELECT(%TM1%,ID,TIME,"[YEAR] = %YR% AND [MONTHNUM] = %MTH%")
*SELECT(%ACC%,[ID],ACCOUNT,"[ACCTYPE] = 'AST' AND [ACCTYPE] = 'LEQ' AND CALC = 'N'")
*XDIM_MEMBERSET ACCOUNT = %ACC%
*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_MEMBERSET TIME = %TM1%,TMVL(-1,%TM1%) //2017.09,2017.08
*DESTINATION_APP = CONSOL_PER
*WHEN TIME
*IS %TM1% //2017.09
*REC(EXPRESSION = %VALUE%) // add value
*IS TMVL(-1,%TM1%) //2017.08
*REC(EXPRESSION = -%VALUE%,TIME = %TM1%) //subtract value
*ENDWHEN```

This code will always subtract the previous month from the current month.

I am assuming that you are running this code as a DM package.

Former Member

Sir,

I am running the script through DM, and it is doing the subtraction between two time periods.

But issue came up when I have data in a Flow type(say 170 in example screen) in previous period 2017.12 but said flow doesn't have data in current period 2018.01. Then this subtraction result gets failed.

Overall conclusion is that the output of this script is based on current period flows only.

Please see the example screen attached.

Need your help in achieving correct subtraction to derive periodic values.

Former Member

Sorry, but there is no scope for FLOW dimension in my script. And my script has scope for both periods:

`*XDIM_MEMBERSET TIME = %TM1%,TMVL(-1,%TM1%) //2017.09,2017.08`

and for previous period it will always subtract value for ANY flow:

```*IS TMVL(-1,%TM1%) //2017.08
*REC(EXPRESSION = -%VALUE%,TIME = %TM1%) //subtract value```

May be you are testing not my script?

Former Member

Sir,

I am testing with your script only, and i am not filtering any Flow type still it is behaving like this.

I am worried if it is the * REC command property that it will only do addition/subtraction within common dimensions only.

Since I tried the script with addition command also but still its picking only the common flow types data only.